VLOOKUP을 열심히 익혔는데, 어느 날 이런 상황을 만납니다:
“상품명으로 상품 ID를 찾고 싶은데… VLOOKUP이 안 되네?”
VLOOKUP은 찾는 값이 범위의 첫 번째 열에 있어야 합니다. 즉, 오른쪽 방향으로만 값을 가져올 수 있죠. 왼쪽에 있는 값을 가져오려면? VLOOKUP으로는 불가능합니다.
이럴 때 등장하는 것이 INDEX MATCH 조합입니다.
INDEX MATCH란?
INDEX와 MATCH는 각각 독립적인 함수입니다. 이 둘을 조합하면 VLOOKUP보다 훨씬 유연한 검색이 가능해집니다.
- MATCH: “찾는 값이 몇 번째 위치에 있는지” 알려줌
- INDEX: “특정 위치의 값을 반환” 해줌
MATCH로 위치를 찾고, INDEX로 그 위치의 값을 가져오는 방식입니다.
MATCH 함수 이해하기
=MATCH(찾을 값, 찾을 범위, [일치 유형])
MATCH는 **“몇 번째에 있어?”**라고 묻는 함수입니다.
| 상품명 |
|---|
| 사과 |
| 바나나 |
| 오렌지 |
이 목록에서 =MATCH("바나나", A2:A4, 0)을 실행하면?
결과는 2입니다. “바나나”가 범위에서 2번째에 있기 때문이죠.
일치 유형 옵션
| 값 | 의미 | 용도 |
|---|---|---|
| 0 | 정확히 일치 | 99% 이것 사용 |
| 1 | 이하에서 가장 큰 값 | 구간 검색 |
| -1 | 이상에서 가장 작은 값 | 역순 구간 검색 |
VLOOKUP의 FALSE처럼, 실무에서는 0(정확히 일치)을 사용하면 됩니다.
INDEX 함수 이해하기
=INDEX(범위, 행 번호, [열 번호])
INDEX는 **“몇 번째 값을 줘”**라고 요청하는 함수입니다.
| 상품 ID |
|---|
| A001 |
| A002 |
| A003 |
이 목록에서 =INDEX(B2:B4, 2)를 실행하면?
결과는 A002입니다. 범위에서 2번째 값을 반환한 것이죠.
INDEX + MATCH 조합
이제 두 함수를 합쳐봅시다.
=INDEX(가져올 범위, MATCH(찾을 값, 찾을 범위, 0))작동 원리:
- MATCH가 “찾을 값”의 위치(행 번호)를 계산
- INDEX가 그 위치에 해당하는 값을 반환
실전 예제: 왼쪽 방향 검색
아래 표에서 “바나나”의 상품 ID를 찾아봅시다.
| A | B |
|---|---|
| 상품 ID | 상품명 |
| A001 | 사과 |
| A002 | 바나나 |
| A003 | 오렌지 |
VLOOKUP은 첫 번째 열(상품 ID)에서 검색해서 오른쪽(상품명)으로만 가져올 수 있습니다. 상품명 → 상품 ID 방향은 불가능합니다.
INDEX MATCH 수식:
=INDEX(A2:A4, MATCH("바나나", B2:B4, 0))분해하면:
MATCH("바나나", B2:B4, 0)→ “바나나”는 B열에서 2번째 → 결과: 2INDEX(A2:A4, 2)→ A열의 2번째 값 → 결과: A002
방향에 관계없이 검색이 됩니다!
VLOOKUP vs INDEX MATCH 비교
| 비교 항목 | VLOOKUP | INDEX MATCH |
|---|---|---|
| 왼쪽 방향 검색 | 불가능 | 가능 |
| 열 삽입 시 | 열 번호 수정 필요 | 영향 없음 |
| 수식 길이 | 짧음 | 상대적으로 김 |
| 성능 (대용량) | 느림 | 빠름 |
| 학습 난이도 | 쉬움 | 약간 어려움 |
언제 INDEX MATCH를 쓸까요?
- 왼쪽 방향 검색이 필요할 때
- 열 추가/삭제가 자주 일어나는 표에서 (열 번호가 밀리지 않음)
- 대용량 데이터에서 성능이 중요할 때
- 검색 열과 반환 열이 연속되지 않을 때
언제 VLOOKUP을 쓸까요?
- 단순한 오른쪽 방향 검색
- 빠르게 수식을 작성해야 할 때
- 동료들이 VLOOKUP에 익숙할 때
자주 쓰는 패턴
패턴 1: 기본형 (단일 열 검색)
=INDEX(반환열, MATCH(찾을값, 검색열, 0))패턴 2: 절대 참조 적용 (수식 복사용)
=INDEX($A$2:$A$100, MATCH(D2, $B$2:$B$100, 0))검색 범위와 반환 범위는 $로 고정하고, 찾을 값만 상대 참조로 두면 아래로 복사해도 올바르게 동작합니다.
패턴 3: 2차원 검색 (행과 열 모두 검색)
=INDEX(데이터범위, MATCH(행검색값, 행머리글, 0), MATCH(열검색값, 열머리글, 0))예를 들어, 월별-지역별 매출 표에서 “3월”과 “서울”이 교차하는 값을 찾을 때 사용합니다.
흔한 실수와 해결법
#N/A 오류
원인: 찾는 값이 없거나, 데이터 형식 불일치 (숫자 vs 텍스트)
해결:
- 값이 실제로 있는지 확인
TRIM()함수로 공백 제거- 숫자/텍스트 형식 통일
#REF! 오류
원인: INDEX 범위와 MATCH 범위의 크기가 다름
해결: 두 범위의 행 개수가 같은지 확인
=INDEX(A2:A100, MATCH(..., B2:B100, 0)) ← 둘 다 99행값이 잘못 나옴
원인: MATCH의 세 번째 인수를 0이 아닌 값으로 설정
해결: 정확한 일치가 필요하면 반드시 0 사용
최신 대안: XLOOKUP
Microsoft 365나 Excel 2021을 사용한다면, XLOOKUP 함수로 더 간단하게 같은 작업을 할 수 있습니다.
=XLOOKUP(찾을값, 검색범위, 반환범위)XLOOKUP은 방향 제한이 없고, 기본값이 정확히 일치이며, 값이 없을 때 표시할 기본값도 지정할 수 있습니다. 다만 구버전 엑셀과 호환되지 않으므로, 파일을 공유해야 한다면 INDEX MATCH가 여전히 안전한 선택입니다.
결론
INDEX MATCH는 처음엔 복잡해 보이지만, 원리를 이해하면 VLOOKUP보다 훨씬 강력하고 유연합니다.
핵심 정리:
- MATCH = “몇 번째에 있어?” (위치 반환)
- INDEX = “몇 번째 값 줘” (값 반환)
- 조합하면 = “A를 찾아서 그 위치의 B를 줘”
VLOOKUP의 한계에 부딪혔을 때, INDEX MATCH를 떠올려 보세요. 왼쪽이든 오른쪽이든, 원하는 방향으로 자유롭게 데이터를 검색할 수 있습니다.