엑셀 INDEX MATCH 함수 사용법 — VLOOKUP이 못 하는 왼쪽 열 찾기
=INDEX(결과 열,MATCH(찾을 값,찾을 열,0)) 수식은 찾는 열이 결과 열보다 오른쪽에 있어도 값을 가져오므로, VLOOKUP으로는 안 되는 "왼쪽 열 찾기"를 해결합니다. 마지막 인수 0은 정확히 일치하는 값만 찾으라는 뜻입니다.INDEX와 MATCH 형식은 어떻게 되나?
두 함수는 역할이 다릅니다. INDEX는 범위에서 "몇 번째 행(열)의 값"을 꺼내고, MATCH는 "찾는 값이 몇 번째에 있는지" 위치 번호를 알려 줍니다. MATCH가 구한 번호를 INDEX에 넘기면 조회 함수처럼 동작합니다.
| 함수 | 형식 | 하는 일 |
|---|---|---|
| INDEX | INDEX(array, row_num, [column_num]) | 범위(array)에서 row_num번째 행, column_num번째 열이 만나는 칸의 값을 돌려줌 |
| MATCH | MATCH(lookup_value, lookup_array, [match_type]) | lookup_array 안에서 lookup_value의 상대적 위치(몇 번째인지)를 돌려줌 |
| 합친 형태 | INDEX(결과 열, MATCH(찾을 값, 찾을 열, 0)) | 찾을 열에서 위치를 구한 뒤, 결과 열의 같은 위치 값을 꺼냄 |
인수 이름은 Microsoft 지원 문서의 영문 표기를 따랐습니다. 한국어 엑셀에서도 함수 이름은 INDEX, MATCH 그대로 입력하며, 지역 설정에 따라 인수 구분자가 쉼표(,) 대신 세미콜론(;)으로 보일 수 있습니다.
Microsoft 문서는 MATCH에 대해 "항목 자체가 아니라 위치가 필요할 때" LOOKUP 계열 대신 쓰라고 안내하며, INDEX의 row_num 값을 MATCH로 구하는 용법을 예로 듭니다. 이 글의 수식이 바로 그 용법입니다. 출처: Microsoft 지원 — MATCH 함수.
이 글에서 쓰는 예제 표
아래 표(A1:D9)를 글 끝까지 씁니다. 첫 행은 머리글이고, 데이터는 2~9행입니다. 이름(B열)으로 사번(A열)을 찾는 것이 목표입니다. 사번은 이름보다 왼쪽에 있습니다.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 사번 | 이름 | 부서 | 급여 |
| 2 | E101 | 김민수 | 영업 | 3200000 |
| 3 | E102 | 이서연 | 인사 | 3500000 |
| 4 | E103 | 박지훈 | 개발 | 4100000 |
| 5 | E104 | 최유진 | 영업 | 3000000 |
| 6 | E105 | 정하늘 | 개발 | 4500000 |
| 7 | E106 | 한도윤 | 총무 | 2900000 |
| 8 | E107 | 오세아 | 인사 | 3300000 |
| 9 | E108 | 윤재희 | 개발 | 3800000 |
급여 단위는 원입니다. 조회할 이름은 F2 칸에 입력한다고 가정합니다(F2에 "박지훈").
왼쪽 열 값을 찾으려면 어떻게 쓰나?
MATCH로 이름의 위치를 먼저 구하고, 그 번호를 INDEX에 넘기면 됩니다. 한 번에 쓰기 전에 두 단계로 나눠 보면 이해가 쉽습니다.
1단계 — MATCH로 위치 구하기
=MATCH("박지훈",B2:B9,0)결과는 3입니다. B2:B9에서 박지훈은 B2(김민수), B3(이서연), B4(박지훈) 순이라 세 번째입니다. 참고로 Microsoft 문서에도 A1:A3의 값이 5, 25, 38일 때 =MATCH(25,A1:A3,0)이 2를 돌려준다는 예가 있습니다.
2단계 — INDEX로 같은 위치의 값 꺼내기
=INDEX(A2:A9,3)결과는 E103입니다. A2:A9의 세 번째 칸이 A4이기 때문입니다.
3단계 — 하나로 합치기
=INDEX(A2:A9,MATCH(F2,B2:B9,0))F2가 "박지훈"이면 결과는 E103입니다. 결과 열(A2:A9)과 찾을 열(B2:B9)은 따로 지정하므로, 어느 쪽이 왼쪽이든 상관없습니다.
| 하고 싶은 일 | 수식 | 결과 |
|---|---|---|
| 이름으로 사번 찾기(왼쪽 열) | =INDEX(A2:A9,MATCH("박지훈",B2:B9,0)) | E103 |
| 이름으로 부서 찾기 | =INDEX(C2:C9,MATCH("박지훈",B2:B9,0)) | 개발 |
| 이름으로 급여 찾기 | =INDEX(D2:D9,MATCH("한도윤",B2:B9,0)) | 2900000 |
| 부서로 이름 찾기(첫 번째 일치) | =INDEX(B2:B9,MATCH("영업",C2:C9,0)) | 김민수 |
한도윤은 B7이라 MATCH 결과가 6이고, D2:D9의 여섯 번째 칸 D7(2900000)이 나옵니다. "영업"은 C2와 C5에 있지만 MATCH는 먼저 나오는 위치(1)만 돌려주므로 김민수가 나옵니다.
같은 값이 여러 번 나오는 열(부서, 이름이 같은 사람 등)을 찾을 열로 쓰면 첫 번째 것만 가져옵니다. 중복 없는 사번·코드 같은 열을 기준으로 삼는 것이 안전합니다. 중복 여부는 엑셀 중복값 찾기와 제거에서 확인하는 방법을 볼 수 있습니다.
행과 열을 모두 MATCH로 찾을 수 있나?
가능합니다. INDEX의 row_num과 column_num에 MATCH를 각각 넣으면, 이름(행)과 항목 머리글(열)을 모두 칸에서 바꿔 가며 조회할 수 있습니다. Microsoft 문서에 따르면 row_num과 column_num을 모두 쓰면 INDEX는 두 위치가 만나는 칸의 값을 돌려줍니다.
=INDEX(A2:D9,MATCH(F2,B2:B9,0),MATCH(G2,A1:D1,0))| F2(이름) | G2(항목) | 행 MATCH | 열 MATCH | 결과 |
|---|---|---|---|---|
| 정하늘 | 급여 | 5 | 4 | 4500000 |
| 정하늘 | 부서 | 5 | 3 | 개발 |
| 윤재희 | 사번 | 8 | 1 | E108 |
행 번호는 B2:B9 안의 위치, 열 번호는 A1:D1 머리글 안의 위치입니다. 정하늘은 B6이라 다섯 번째, "급여"는 D1이라 네 번째입니다. A2:D9의 5행 4열은 D6(4500000)입니다.
이 방식은 열 번호를 숫자로 직접 적지 않아도 되어, 항목 열 순서가 바뀌어도 수식을 고치지 않아도 됩니다.
VLOOKUP 대신 INDEX MATCH를 쓰는 이유는?
VLOOKUP은 표의 맨 왼쪽 열에서 값을 찾기 때문에, 찾는 열보다 왼쪽에 있는 값은 가져올 수 없습니다. INDEX MATCH는 찾을 열과 결과 열을 따로 지정하므로 이 제한이 없습니다. VLOOKUP의 기본 인수는 VLOOKUP 사용법에서 정리했습니다.
| 상황 | VLOOKUP | INDEX MATCH |
|---|---|---|
| 이름(B열)으로 사번(A열) 찾기 | =VLOOKUP("박지훈",A2:D9,1,FALSE)는 A열에서 이름을 찾으므로 #N/A | =INDEX(A2:A9,MATCH("박지훈",B2:B9,0)) → E103 |
| 결과 열 지정 방식 | 표 범위 안에서 몇 번째 열인지 숫자로 지정 | 결과 열 범위를 직접 지정 |
| 표 중간에 열을 끼워 넣을 때 | 숫자(열 번호)는 자동으로 바뀌지 않음 | 범위 참조가 열 삽입에 맞춰 이동 |
| 수식 길이 | 짧음 | 함수 2개라 다소 김 |
첫 행의 VLOOKUP 예: 4번째 인수 FALSE는 정확히 일치를 뜻합니다. A열에는 "박지훈"이 없으므로 #N/A가 나옵니다.
새 버전을 쓴다면 XLOOKUP이라는 대안도 있습니다. Microsoft 문서도 많은 조회 상황에서 XLOOKUP이 INDEX와 MATCH보다 단순한 대안이라고 안내합니다. 어느 쪽이 맞는지는 XLOOKUP과 VLOOKUP 차이에서 비교했습니다. 다만 XLOOKUP이 없는 버전의 파일을 공유하는 경우에는 INDEX MATCH가 여전히 쓸모 있습니다.
MATCH의 세 번째 인수 0, 1, -1은 무엇이 다른가?
세 번째 인수(match_type)가 0이면 정확히 일치하는 값만 찾고, 1이나 -1이면 근삿값을 찾습니다. 정확한 이름·코드를 찾는 일이라면 거의 항상 0을 씁니다. Microsoft 문서의 예시를 표로 정리합니다.
| 수식 | 범위 B2:B5(25, 38, 40, 41) | 결과 | 설명 |
|---|---|---|---|
=MATCH(39,B2:B5,1) | 오름차순 | 2 | 39와 같은 값이 없어 바로 아래 값(38)의 위치를 돌려줌 |
=MATCH(41,B2:B5,0) | - | 4 | 정확히 일치하는 41의 위치 |
=MATCH(40,B2:B5,-1) | 내림차순이 아님 | #N/A | -1은 값이 내림차순일 때만 사용 가능 |
출처: Microsoft 지원 — MATCH 함수의 예시 표. 이 표는 이 글의 예제 표(A1:D9)와는 별도의 데이터입니다.
0을 빠뜨리고 =MATCH(F2,B2:B9,)처럼 쓰거나 인수를 생략하면 정확히 일치하는 검색이 아닙니다. 정렬되지 않은 데이터에서는 엉뚱한 위치가 나올 수 있으므로, 정확한 값을 찾을 때는 0을 항상 적습니다.
결과가 이상하거나 오류가 날 때
INDEX MATCH의 오류는 대부분 MATCH가 위치를 못 구했거나, 두 범위의 크기와 시작 행이 어긋난 경우입니다. 아래 순서로 확인합니다.
| 증상 | 원인 | 확인 방법 |
|---|---|---|
| #N/A | 찾을 값이 찾을 열에 없음(철자, 공백, 숫자와 텍스트 불일치) | 수식 안의 MATCH만 따로 입력해 #N/A가 나오는지 확인합니다. 칸에 앞뒤 공백이 있는지, 숫자가 텍스트로 저장됐는지 점검합니다. |
| #REF! | row_num이나 column_num이 범위 밖을 가리킴 | =INDEX(A2:A9,9)처럼 8칸짜리 범위에 9번째를 요청하면 #REF!가 나옵니다. 범위 크기를 확인합니다. |
| 값은 나오는데 엉뚱한 사람의 값 | INDEX 범위와 MATCH 범위의 시작 행이 다름 | 두 범위가 같은 행에서 시작해 같은 길이인지 봅니다. |
| #VALUE! | 찾는 값이 255자를 넘는 경우 등 | Microsoft 문서에 따르면 이런 경우 배열 수식으로 입력해야 하며, Microsoft 365에서는 Enter, 이전 버전에서는 Ctrl+Shift+Enter로 확정합니다. |
| #N/A가 일부 행에서만 나옴 | 일부 값만 철자나 형식이 다름 | #N/A가 난 행의 찾을 값을 복사해 찾을 열의 값과 길이(LEN)를 비교합니다. |
#REF!와 #VALUE! 항목은 Microsoft 지원 문서(INDEX 함수, INDEX/MATCH의 #VALUE! 오류 안내)에 있는 동작을 정리한 것입니다.
범위가 어긋난 예
예제 표에서 결과 열을 한 행 밑으로 잘못 잡은 경우를 봅니다.
=INDEX(A3:A10,MATCH("박지훈",B2:B9,0))MATCH는 3을 돌려주지만 INDEX는 A3부터 세므로 세 번째 칸은 A5이고, 결과는 E104(최유진의 사번)가 됩니다. 정답은 E103입니다. 오류 표시 없이 틀린 값이 나오므로 더 위험합니다. 처음에는 두 범위의 첫 칸 행 번호가 같은지 꼭 확인합니다.
찾는 값이 없을 때 문구로 바꾸기
명단에 없는 이름을 조회해도 #N/A 대신 문구를 보이고 싶다면 IFERROR로 감쌉니다.
=IFERROR(INDEX(A2:A9,MATCH("김철수",B2:B9,0)),"없음")김철수는 B열에 없으므로 결과는 없음입니다. 다만 IFERROR는 모든 오류를 가려서 범위 실수까지 숨길 수 있습니다. 사용 시 주의점은 엑셀 IFERROR 함수 사용법에 정리했습니다. 수식을 완성한 뒤 마지막 단계에서 감싸는 것이 좋습니다.