엑셀

엑셀 MATCH 함수 이해하기

콘파냐 2017. 4. 18. 15:48
반응형

MATCH 함수는 엑셀에서 활용도가 높은 편입니다. 단독으로 쓰이기도 하지만 INDEX함수와도 자주 같이 쓰입니다. 간단한 함수 같지만 몇가지 까다로운 점이 있어서 기본적인 정리를 하려고 합니다.

MATCH함수의 컨셉은 대략 이렇습니다. 지정된 셀 범위에서 특정한 값이 있는 위치가 어디냐?를 찾는 것 입니다.

이 때 주의할 점이 있는데 지정된 범위는 하나의 행 또는 하나의 열이됩니다. 예를들어 범위가 F6:G9 면 안되겠죠. F6:F9거나(하나의 열), F6:I6과(하나의 행) 같은 형태여야 합니다. 만약 두 개 이상의 행이나 열을 범위에 두고 싶다면 배열 수식을 사용해야 합니다.


이렇게 인수에 범위를 지정하는 부분과 매칭될 값을 넣는 부분이 있을 거란 걸 짐작할 수 있겠습니다.


MATCH 함수



찾으려는 값찾을 범위는 앞에서 간단히 언급했습니다. 각각 첫번째 두번째 인자로 지정되고 세 번째 인자는 매치타입입니다. 매치 타입은 정확히 일치, 보다 작음, 보다 큼 이렇게 세가지 선택이 가능한데 자세한 설명은 차차하겠습니다.


우선 찾을 범위의 형태에 대한 예를 몇가지 들어보도록 하겠습니다.


예1) 하나의 열 또는 하나의 행을 범위로 선택하는 경우

▲ 수식에 MATCH함수를 쓴 후 찾을 값을 5, 찾을 범위를 B3:D3으로 두겠습니다. 

그리고 다음 그림처럼 세번째 인자인 매치타입은 세가지 선택이 가능합니다.(생략하면 기본값 1입니다.)


  • 1 - 보다작음 : 범위에 있는 값들이 오름차순으로 정렬되어 있어야 한다. (정확히 일치하는 값 만약 없다면 그 보다 작은 값 중에 가장 가까운 값을 찾는다.) (기본값)
  • 0 - 정확히 일치 : 말그대로 정확히 일치하는 값을 찾음
  • -1 - 보다 큼 : 범위에 있는 값들이 내림차순으로 정렬되어 있어야 한다. (정확히 일치하는 값 만약 없다면 그 보다 큰 값 중에 가장 가까운 값을 찾는다.

이 예에서는 세번째 인자를 0으로 두어서 정확히 일치하는 값 5를 찾습니다. 그리고 2라는 값이 반환되었습니다. 

2라는 값은 두번 째 인자인 범위(B3:D3)에서 찾는 값의 위치를 말해줍니다. 5라는 값은 각각 2, 5, 8 의 값을 갖는 총 3개의 열 중에 2번째 열에 있는 값이므로 2가 반환된 겁니다.



예2) 여러 범위를 선택하는 경우

이런 경우는 배열 수식을 이용해서 여러 조건이 모두 들어맞는 열, 또는 행을 찾는데 사용합니다. 

배열 수식에 대한 내용 참고

2017/04/20 - [엑셀] - 엑셀 배열수식에 대한 이해




위 표에서 나이가 27살이고 도시가 부산인 경우의 열을 찾아보도록 합시다. MATCH함수를 사용하려면 하나의 열만 선택해야하는데 여기에서는 두 개의 열을 선택해야 하니 다른 방법이 필요합니다. 그래서 배열 수식을 사용합니다.


▲ 위 식은 여러개의 조건을 만족하는 행의 값을 찾아주는 식입니다. 이 식이 어떻게 동작하는지 이해하기 위해서 배열 수식을 제대로 알고 있어야 합니다.  우선 배열 수식을 만들려면 수식을 입력한 후에 엔터를 누르지 말고 대신 Ctrl+Shift+Enter 를 눌러줍니다. 이렇게 누르면 위 그림과 같이 수식을 감싸는 {수식} 중괄호가 생깁니다. 이렇게 수식이 배열수식이 되면 범위간에 연산이 가능해집니다. 단 연산에 쓰일 범위간에 형태는 같아야겠죠. 예를들어 1행 10열의 범위 + 1행 10열의 범위 처럼 말이죠.


위 식 역시 (B3:B10=27)*(C3:C10="부산") 이라는 범위간에 연산이 있습니다. 그리고 범위 간에 상대적으로 동일한 위치에 있는 요소간에 계산이 됩니다. 좀 생소할 수도 있으므로 차근차근 따져보겠습니다.

먼저 B3의 셀이 27인기 검사해서 맞다면 1 틀리면 0을 반환합니다. 이와 동시에 C3의 셀이 "부산"인지 아닌지에 때라 1 또는 0을 반환하죠.

만약 이 두 조건이 모두 맞다면 둘 다 1을 반환하여 *(곱셈)의 결과 1이 반환됩니다. 이렇게 계산된 결과는 배열의 요소로 저장되게 됩니다. 이렇게 해서 위 식의 계산 결과는 다음과 같은 식이 됩니다.


=MATCH(1, {0;0;0;1;0;0;0;0},0)

{0;0;0;1;0;0;0;0}은 8행1열의 배열을 나타냅니다. 다음과 같은 것이죠. (자세한 것은 배열 수식에 대한 포스팅을 참고해 주세요.)

데이터간에 ;(세미콜론으로 구분되어 있다면 행을 구분해 주고 만약 ,(콤마)로 나뉘었다면 열을 구분해 줍니다.

어찌되었건 위 식을 해석하면 해당 배열에 1의 값의 위치가 어디있냐는 것입니다. 즉 27살에 "부산"인 행을 찾는 것과 동일합니다. 그래서 4가 반환된 겁니다.

반응형