거래처 코드로 담당자를 찾거나 품목 코드로 단가를 가져오는 일은 엑셀 실무에서 가장 자주 하는 작업이다. 이전 글에서 XLOOKUP을 다뤘지만, 회사 PC의 엑셀 버전이 낮아 XLOOKUP이 아예 없는 경우도 적지 않다.
그럴 때 쓰는 조합이 INDEX와 MATCH다. 두 함수를 따로 보면 어렵지 않고, 한 번 구조를 익혀 두면 VLOOKUP의 한계를 대부분 넘을 수 있다. 이 글에서는 두 방식의 구조와 차이, 상황별 선택 기준을 정리한다.
1. INDEX와 MATCH의 역할
MATCH는 찾는 값이 범위에서 몇 번째인지 위치를 알려주고, INDEX는 범위에서 그 위치의 값을 꺼낸다. 둘을 합치면 코드가 몇 번째 행인지 찾은 뒤 그 행의 단가를 가져오는 동작이 된다.
| 함수 | 구조 | 하는 일 |
|---|---|---|
| MATCH | =MATCH(찾을값, 찾을범위, 0) | 정확히 일치하는 값의 위치 번호 |
| INDEX | =INDEX(반환범위, 행번호) | 범위에서 해당 번호의 값 |
| 결합 | =INDEX(C:C, MATCH(F1, A:A, 0)) | A열에서 F1을 찾아 C열의 같은 행 값 반환 |
2. VLOOKUP이 못하는 일
VLOOKUP은 찾는 값이 표의 맨 왼쪽에 있어야 하고 반환할 열을 번호로 지정한다. 그래서 중간에 열을 끼워 넣으면 번호가 어긋나 결과가 틀어진다. INDEX·MATCH는 반환 범위를 직접 지정하므로 찾는 열이 오른쪽에 있어도 되고 열 삽입에도 영향을 받지 않는다.
| 비교 항목 | VLOOKUP | INDEX·MATCH | XLOOKUP |
|---|---|---|---|
| 왼쪽 열 값 반환 | 불가 | 가능 | 가능 |
| 열 삽입 시 | 열 번호가 어긋날 수 있음 | 영향 없음 | 영향 없음 |
| 찾지 못했을 때 | #N/A | #N/A, IFERROR로 처리 | 인수로 문구 지정 |
| 사용 가능 버전 | 모든 버전 | 모든 버전 | Excel 2021, 365 이상 |
3. 행과 열을 교차해서 조회하기
INDEX에 행 번호와 열 번호를 함께 주면 표의 교차 지점 값을 가져올 수 있다. 품목별, 월별 출고량이 정리된 표에서 특정 품목의 특정 월 값을 찾을 때 유용하다.
| 셀 | 내용 |
|---|---|
| G1 | 품목명 입력 |
| G2 | 월 입력 (예: 10월) |
| G3 | =INDEX(B2:E10, MATCH(G1, A2:A10, 0), MATCH(G2, B1:E1, 0)) |
4. 실무에서의 선택 기준
파일을 혼자 쓰는지, 여러 사람이 공유하는지에 따라 선택이 달라진다. 공유 파일에서 상대방의 엑셀 버전을 알 수 없다면 모든 버전에서 동작하는 INDEX·MATCH가 안전하다. 반대로 본인만 쓰는 365 환경이라면 수식이 짧은 XLOOKUP이 읽기 쉽고 오류도 적다.
| 상황 | 추천 방식 | 이유 |
|---|---|---|
| 여러 사람이 쓰는 공유 파일 | INDEX·MATCH | 구버전에서도 계산 오류가 없다 |
| 본인만 쓰는 365 환경 | XLOOKUP | 수식이 짧고 찾지 못했을 때 문구를 바로 지정 |
| 열 삽입이 잦은 표 | INDEX·MATCH 또는 XLOOKUP | 열 번호 의존이 없다 |
| 단순한 오른쪽 조회 | VLOOKUP | 이미 익숙하면 그대로 써도 무방 |
핵심정리
- MATCH는 위치를 찾고 INDEX는 그 위치의 값을 꺼낸다.
- MATCH의 마지막 인수는 0으로 고정한다.
- 구버전 엑셀이거나 열 삽입이 잦은 표는 INDEX·MATCH, 365 환경은 XLOOKUP이 편하다.
- 찾지 못할 때 보여줄 문구는 IFERROR로 지정한다.
다음 글에서는 IF·IFS·SWITCH로 조건에 따라 결과를 나누는 방법을 정리한다. 막히는 수식이 있다면 댓글로 남겨 주면 예제로 다루겠다.
이 글의 표와 수식 예제에 쓰인 데이터는 설명을 위해 만든 가상 예시입니다.
