엑셀 MATCH 함수 이해하기

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가 반환된 겁니다.

반응형

'엑셀' 카테고리의 다른 글

엑셀 빈셀 삭제  (2) 2017.04.21
엑셀 배열수식에 대한 이해  (10) 2017.04.20
엑셀 MATCH 함수 이해하기  (16) 2017.04.18
엑셀 자동합계 이해하기  (2) 2017.04.12
엑셀 csv 파일 이해하기  (0) 2017.04.10
엑셀 행렬 다루기  (2) 2017.04.07
이 댓글을 비밀 댓글로
    • 1
    • 2017.06.27 19:03
    개어렵다 와 난 멍청이인가
    • 그렇지 않습니다. 처음엔 어려워보여도 여러번 반복하면 눈에 들어올겁니다.~
    • YOUNG
    • 2017.07.24 22:02
    설명정말잘하셔요 감사합니다
    • 그런가요? 다행이네요~ 감사합니다.^^
    • hee
    • 2017.09.27 14:15
    포스팅에 설명이 잘 되어있네요! 감사드립니다..
    질문이 있는데요, '예2' 번에서 lookupvalue 자리에 들어가있는 '1'은 뭘 의미하는건가요?? ^^;;
    • 포스팅에 있는 설명을 다시 읽어보세요. 제가 더 자세히 설명드리기 힘들 것 같네요.

      간략히
      (B3:B10=27)*(C3:C10="부산";)의 결과는 0 이나 1 둘 중 하나입니다.
      질문하신 1은 위 수식의 결과가 1이 나오는 경우라고 할 수 있겠습니다.
    • 평생학습
    • 2018.02.14 11:20
    잘 보았습니다. 자세한 설명이 이해에 도움이 됩니다.

    내용 중 질문이 있습니다. 위 예1)에서 MATCH(5,B3:D3,0) 부분인데요. B3:D3부분은 MATCH함수의 범위는 하나의 열 또는 하나의 열범위로만 작성해야 한다(행과열 범위는 배열 수식으로)는 초반 부분 설명과 서로 충돌하는네요. 범위를 행과 열로 지정해 줘도 무방한 건가요?
    • 2018.05.21 22:01
    와.. 미쳤다... 정말 이해가 잘되서 묵은게 내려가는 사이다! 정말 감사합니다ㅠㅠㅠㅠ
    • 익명
    • 2018.05.21 22:19
    비밀댓글입니다
    • 2018.05.21 23:48
    그리고 혹시 excel image assistant 라는 프로그램 아시는지요? 셀의 이름값과 파일의 이미지 이름이랑 동일하게 해주는 프로그램인데, 혹시 프로그램 사용 없이 vba로 구현가능한가요?
    • 엑셀린이
    • 2018.08.06 01:04
    그러니까 순서가 먼저 B열(나이)와 D열(지역) 각각을 모두 0,1로 데이터를 전환 후에 배열 수식 으로 곱하기 해서 match 함수 서식에 넣는건가요?
    • HA
    • 2018.11.06 15:13
    상세한 설명 감사합니다 ㅠㅠ
    • 와우
    • 2018.11.16 08:20
    감사합니다. vlookup 함수와 match문제에서 match값을 왜 +1 더 주는지 계속 고민했는데 글 보자마자 이해했어요!
    • 나그네
    • 2019.04.23 23:21
    설명이 확 와닿네요. 왜 match에서 배열을 쓰면 match(1, 이런식으로 시작하는 지 이 글을 읽고 이제 알았네요.. 감사합니다
    • 멍충이
    • 2019.05.10 14:48
    베댓에 공감. 개어렵다. 10년넘게 엑셀을 썼구만. 난 멍청이다.
    • 몰디브
    • 2019.06.13 16:08
    관리자의 승인을 기다리고 있는 댓글입니다