Home > 엑셀 함수 > 자주 사용하는 함수 > MATCH 함수 - 특정 범위에서 값의 위치 찾기

MATCH 함수 - 특정 범위에서 값의 위치 찾기

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

구문(Syntax)

MATCH(lookup_value, lookup_array, [match_type])

지정된 범위에서 찾고자 하는 값의 위치를 반환한다.

인수 :

  • lookup_value : 찾고자 하는 값
  • lookup_array : 값을 찾을 범위
  • match_type : 일치 유형
    1 또는 생략 lookup_value와 정확히 일치하거나  작은 것을 찾음, lookup_array 는 오름차순으로 정렬되어 있어야 함
    0 lookup_value와 정확히 일치하는 것을 찾음, lookup_array 는 정렬되어 있지 않아도 됨
    -1 lookup_value와 정확히 일치하거나  큰 것을 찾음, lookup_array 는 내림차순으로 정렬되어 있어야 함

 

사용예 :

1) 원하는 값이 있는 위치 찾기

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

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

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

MATCH함수로 위치 찾기

 

2) 근사값의 위치 찾기

위의 예에서는 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(83,B4:B12,1)을 입력했는데

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

 

3) 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함수를 이용하지 않고 VLOOKUP함수를 이용해서 간단히 해결할 수도 있다. 그러나 취득점수가 첫번째 열에 있지 않고 두번째 열에 있다면 VLOOKUP함수로 해결이 불가능한다. 이 때는 아래와 같이 INDEX, MATCH함수를 함께 사용하면 위력을 발휘한다. VLOOKUP을 이용하면 값을 찾지 못하고 #N/A오류가 발생한다.

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

 

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

 

 

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

 

“MATCH 함수 - 특정 범위에서 값의 위치 찾기”의 17개의 댓글

  1. 핑백: VLOOKUP으로 안될때 INDEX,MATCH 사용하기 - XLWorks

  2. 도와주세요

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

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

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

  3. 도와주세요

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

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

    1. Match함수에 대해 여쭙니다 이럴때는 어찌해야하는지요
      아래와같이 값을 찿을 범위가 참조영역이 변할때마다 범위를 변경하고자 한다면
      예를 들어 두셀에 숫자가 E1=3, F1=5 들어있다면
      =match(a2,b&e1:f&f1,0) 하면 에러 발생합니다 . 즉 범위가 B3:F5가 되겠죠
      참조범위가 변함에 따라 찿는 범위가 자동으로 변경되도록 할수있는 방법은 없는지요

  4. 감사합니다ㅠㅠ 업무에 해당 INDEX, MATCH 함수사용방법이 필요했는데 큰도움이 되었습니다 !!!

  5. Match함수에 대해 여쭙니다 이럴때는 어찌해야하는지요
    아래와같이 값을 찿을 범위가 참조영역이 변할때마다 범위를 변경하고자 한다면
    예를 들어 두셀에 숫자가 E1=3, F1=5 들어있다면
    =match(a2,b&e1:f&f1,0) 하면 에러 발생합니다 . 즉 범위가 B3:F5가 되겠죠
    참조범위가 변함에 따라 찿는 범위가 자동으로 변경되도록 할수있는 방법은 없는지요

    1. 안녕하세요. 참조를 동적으로 바꾸는 INDIRECT함수를 사용하면 됩니다.
      =match(a2,indirect("b"&e1&":"&"f"&f1),0)
      혹시 위 수식을 복사해서 붙여넣었는데 오류가 나면 쌍따옴표를 키보드에 있는 쌍따옴표로 다시 입력해보세요.
      INDIRECT함수에 대해서는 다음 링크를 참고하세요.
      https://xlworks.net/excel-function-indirect/
      감사합니다.

  6. 안녕하세요. 급여명세서를 만들고 있는데요,
    예를들어 그중 11월급여시트에 과세금액이 2,000,000원 나오면
    간이세액표 시트에서 2,000,000 ~ 2,010,000 구간을(칸이 200 과 210 각각 나눠있음) 찾아
    11월급여시트에 있는 공제가족수(1,2,3등값이 있음)에 해당하는 값을(간이세액표 시트의 구간옆에 각 공제가족수1이면 얼마,2면 얼마 나눠있음)
    11월급여시트의 소득세란에 넣으려고 합니다.
    이럴땐 어떤 함수를 어떻게 이용해야 하는지요?

  7. 안녕하세요.엑셀 강의 큰 도움되고 있어서 정말 감사합니다!

    혹시 원하는 텍스트 영역 예를 들면 t열에 있는 텍스트 들이
    c열에 있을 때. c열에 있는 동일한 텍스트가 볼드처리 되거나 텍스트 섹상이 변하게 적용하고 싶은데
    가능한 부분일까요?

    1. 안녕하세요. 조건부 서식을 이용하면 됩니다.
      예를 들어 C2셀과 T2셀에 동일한 텍스트가 있다고 할 때 다음과 같이 하면 됩니다.
      1. 조건부 서식 추가 : C2셀을 마우스로 선택한 후 리본메뉴 > 홈 탭 > 스타일 그룹 > 조건부 서식 클릭 > 셀강조 규칙 > 같음(E)... > 팝업화면에서 왼쪽 빈칸에 "=$T2"를 입력하고 오른쪽 서식은 원하는 것을 선택
      2. 다른 셀에도 조건부 서식 복사 : C2셀을 선택한 후 "서식 복사" > C3부터 C10까지 동일한 서식을 적용한다고 했을 때 C3:C10을 선택한 후 서식 붙여넣기

  8. 안녕하세요 항상 엑셀강의 잘보고있습니다. 감사합니다.
    질문이 있습니다만
    특정인물의 근무시간표를 만들고있습니다.
    홍길동, 김민수, 이민수, 박민수 등등
    있다고 쳤을때 홍길동의 근무시간만 찾아내서 계산하는것까진 했습니다
    ({=SUM(($A$5:$A$26=$AX1)*($G$5:$AK$26))}
    (a5:a26 가 명단리스트 ax1 이 홍길동 g5 : ak26가 근무시간 기록하는 곳 )
    여기서 문제가...일반 근무의경우 그대로 인건비를 곱하면되는데
    잔업의경우 1.25 를 곱해야되요.
    잔업시간을 계산해야되는데 좋은방법이 없을까요 ?
    홍길동의 8시간이상근무시에는 9는 한시간 10은 두시간 등등으로 전부 더하기 위해선 어떻게 해야되나요?

    1. 안녕하세요.
      잔업시간은 다음 수식으로 구하세요. 배열수식이므로 수식입력 후 ctrl+shift+enter해주세요.
      =SUM(($A$5:$A$26=$AX1)*IF(($G$5:$AK$26)>8,($G$5:$AK$26)-8,0))
      개인별 근무시간이 8시간이 넘으면 근무시간에서에서 8을 빼고, 넘지 않으면 0을 반환하도록 해서 다 더하면 개인별 잔업시간이 됩니다.

  9. 조건부 서식을 이용해서 수치의 범위를 지정(10~20)하고 값을 입력했을때 (10) 입력한 값을 빨간색으로 표시하는건 할수있는데
    그 범위를 변경하였을때(20~30) 똑같은 값을 입력했을 경우 바로 적용 가능하게 하는 수식이나 조건부서식이 있을까요?

    1. 조건부 서식을 A열에 적용한다고 했을 때 B1셀에 범위의 시작값, B2셀에 범위의 끝값을 입력하고 조건부서식의 수식을 다음과 같이 입력하면 될것 같습니다.
      =AND(A1>=$B$1, A1<=$B$2)

답글 남기기

이메일 주소는 공개되지 않습니다.