엑셀함수 MATCH – 특정 범위에서 값의 위치 찾기 – XLWorks

공개됨 글쓴이 Admin댓글 5개

엑셀의 MATCH함수는 지정된 범위에서 찾고자 하는 값의 위치를 반환한다. 단순히 위치만 찾아 주므로 위치만 가지고 별로 쓸일이 없다. INDEX 등 다른 함수와 함께 사용해서 성적 등급을 계산 하는 등 응용해서 많이 사용되므로 함수의 구조를 잘 이해해  두면 복잡한 문제 해결 시 많은 도움이 된다 .

구문(Syntax)

 

사용예 :

원하는 값이 있는 위치 찾기

아래의 자료 취득점수 영역에서 90점의 위치를 가져오기 위해서 F4셀에 =MATCH(90,B4:B12,0)을  입력해보자.

  • 첫번째 인수 90은 위치를 찾기 위한 값이다.
  • 두번째 인수 B4:B12는 값을 찾기 위한 범위이다.
  • 세번째 인수 0은 값을 찾는 조건이다. 0은 일치하는 값의 위치를 찾는다는 의미이다.

수식이 정상적으로 입력되었으면 F4셀에 취득점수 범위(B4:B12)에서의 90점의 위치인 8이 표시될 것이다. 시트의 행번호(11)가 아니라 지정한 범위의 행번호(8)라는 점을 주의하자

MATCH함수로 위치 찾기
MATCH함수로 위치 찾기

 

근사값의 위치 찾기

위의 예에서는 90점이 위치해 있는 위치를 찾았다.

이번에는 찾는 값이 범위에 없을 경우에 근사값(가까운 값, 비슷한 값)의 위치를 찾아보자. 83점과 가까운 값을 찾되 83점보다 작거나 같은 값의 위치를 찾기 위해 F5셀에 =MATCH(83,B4:B12,1)를 입력 입력해보자.

  • 첫번째 인수 83은 위치를 찾기 위한 값이다.
  • 두번째 인수 B4:B12는 값을 찾기 위한 범위이다.
  • 세번째 인수 1은 값을 찾는 조건이다. 1은 입력한 값과 가장 가까운 값을 찾되 작거나 같은 값의 위치를 찾는다는 의미이다. 찾는 범위에서 83점과 가까운 값은 80점과 85점인데 85점은 83점보다 크므로 제외되고 80점이 83점보다 작거나 같으므로 80점의 위치를 찾는다.

수식이 정상적으로 입력되었으면 F5셀에 83점과 가까운 값 중에 작거나 같은 값인 80점의 위치 6이 표시된다

MATCH함수로 근사값의 위치 찾기
MATCH함수로 근사값의 위치 찾기

 

*근사값을 찾을 때 주의할 점 : 

이번 예에서 =MATCH(83,B4:B12,1)을 입력했는데

  • 마지막 인수 1은 작거나 같은 값을 찾는 조건이므로 찾는 범위의 값은 오름 차순으로 정렬이 되어 있어야 한다. 오름 차순 정렬은 값이 위에서 부터 1, 2, 3, 4… 식으로 작은 값부터 큰 값 순서로 정렬이 된다는 의미이다. 정렬이 되어 있지 않으면 값을 찾지 못하고 #N/A 이 표시된다.
  • 마지막 인수가 -1이면 내림차순 정렬이 되어 있어야 하고 이때는 가까운 값 중에 크거나 같은 값을 찾는다는 의미이다. 내림차순 정렬은 값이 위에서부터 4,3,2,1… 식으로 큰 값부터 작은 값 순서로 정렬이 된다는 의미이다. 정렬이 되어 있지 않으면 값을 찾지 못하고 #N/A 이 표시된다.

 

MATCH함수 응용 – INDEX함수와 결합하여 성적등급 산출하기

위에서 점수가 있는 위치를 열심히 찾았는데 사실 점수의 위치를 찾아서는 쓸일이 별로 없다. 특정 범위에서 행과 열만 알면 값을 찾아 주는 INDEX함수와 함께 사용해서 성적등급을 산출해 보자.
INDEX함수 사용법은 엑셀함수 INDEX – 특정 범위에서 행과 열을 이용하여 값 찾기를 참고한다.

F9셀에 =INDEX(C4:C12,MATCH(F7,B4:B12,1),1)를 입력 입력해보자.

  • 첫번째 인수 C4:C12는 성적등급을 찾기 위한 범위이다.
  • 두번째 인수는 INDEX함수에서 값을 찾는 행을 의미한다. 이 예에서는 MATCH함수를 이용하여 행의 위치를 찾는다. =MATCH(F7,B4:B12,1)는 취득점수가 있는 가까운 값의 행의 위치를 찾는다. MATCH함수에서
      – 첫번째 인수 F7은 실제 취득점수 92점이 입력된 셀의 위치이다.
      – 두번째 인수 B4:B12는 값을 찾기 위한 범위이고
      – 세번째 인수 1은 1은 값을 찾는 조건이다. 1은 입력한 값과 가장 가까운 값을 찾되 작거나 같은 값의 위치를 찾는다는 의미이다.
  • 세번째 인수 1은 INDEX함수에서 값을 찾는 열을 의미한다. 첫번째 인수에서 범위를 C4:C12입력했으므로 첫번째 열에 성적등급이 위치해 있다. 따라서 1을 입력한다.
INDEX, MATCH함수 응용
INDEX, MATCH함수 응용

 

팁! : 사실 위의 예에서는 복잡한 INDEX, MATCH함수를 이용하지 않고 VLOOKUP함수를 이용해서 간단히 해결할 수도 있다. 그러나 취득점수가 첫번째 열에 있지 않고 두번째 열에 있다면 VLOOKUP함수로 해결이 불가능한다. 이 때는 아래와 같이 INDEX, MATCH함수를 함께 사용하면 위력을 발휘한다. VLOOKUP을 이용하면 값을 찾지 못하고 #N/A오류가 발생한다.

VLOOKUP으로 안될 때 INDEX,MATCH이용하기
VLOOKUP으로 안될 때 INDEX,MATCH이용하기

 

이것으로 MATCH함수에 대해 설명을 마친다. 백견이 불여일타.. 눈으로 보는 것보다 한번 입력해 보는 것이 실력이 느는 지름길… 첨부의 실습자료를 다운로드 받아서 실습해 보자.

[ 엑셀 찾기 및 참조영역 함수 목록 ]

 

엑셀함수 MATCH – 특정 범위에서 값의 위치 찾기 – XLWorks에 1개의 응답

  1. 안녕하세요 ㅠㅠ
    1이란 숫자 입력시 a2:c2 범위에 X값이 포함되어있다면 “예”
    2란 숫자가 입력시 a2:c2 →(범위변경) a3:c3 범위에서 X값이 포함되어있는지 검색
    3란 숫자가 입력시 a2:c2 →(범위변경) a4:c4 범위에서 X값이 포함되어있는지 검색
    4란 숫자가 입력시 a2:c2 →(범위변경) a5:c5 범위에서 X값이 포함되어있는지 검색

    이걸..찾기위해서 한참을 강의를 찾아보는데 ㅠㅠ 어렵네요
    고수님 혹시 도움좀 부탁드려도 될까요?

    • 안녕하세요. 댓글의 내용만으로는 명확하지 않은 부분이 있습니다. 파일을 admin@xlworks.net으로 보내주시면 확인해 보겠습니다.
      감사합니다.

  2. 안녕하세요 ㅠㅠ
    1이란 숫자 입력시 a2:c2 범위에 X값이 포함되어있다면 “예”
    2란 숫자가 입력시 a2:c2 →(범위변경) a3:c3 범위에서 X값이 포함되어있는지 검색
    3란 숫자가 입력시 a2:c2 →(범위변경) a4:c4 범위에서 X값이 포함되어있는지 검색
    4란 숫자가 입력시 a2:c2 →(범위변경) a5:c5 범위에서 X값이 포함되어있는지 검색

    이걸..찾기위해서 한참을 강의를 찾아보는데 ㅠㅠ 어렵네요
    고수님 혹시 도움좀 부탁드려도 될까요?

댓글 남기기

이메일은 공개되지 않습니다.