안녕하세요! 여러분의 업무 시간을 절반으로 줄여드리는 엑셀 파트너입니다.
우리는 지금까지 VLOOKUP 함수가 얼마나 대단한지 배웠습니다. 하지만 안타깝게도 VLOOKUP에게는 치명적인 약점이 하나 있습니다.
"기준값보다 왼쪽에 있는 데이터는 가져올 수 없다."
예를 들어, [사번 | 이름] 순서가 아니라 [이름 | 사번] 순서로 되어 있는 표에서, 사 번을 기준으로 이름을 찾으려고 하면 VLOOKUP은 작동하지 않습니다. 억지로 표의 순서를 바꾸거나 데이터를 복사해서 옮겨야만 했죠.
오늘 소개할 INDEX와 MATCH 함수 조합은 이런 VLOOKUP의 한계를 비웃듯, 상하좌우 어디에 있는 데이터든 자유자재로 찾아내는 강력한 기술입니다. 조금 어렵게 느껴질 수도 있지만, 이 고비만 넘기면 여러분은 진정한 엑셀 마스터의 길로 들어서게 됩니다.
1. 기초 다지기: 각개전투 (INDEX와 MATCH의 역할)
두 함수를 합치기 전에, 각각 무슨 일을 하는 녀석인지 아주 쉽게 알아보겠습니다.
1-1. INDEX 함수: 좌표 찍기
INDEX는 "특정 범위에서 몇 번째 줄, 몇 번째 칸에 있는 값을 가져와!"라고 명령하는 지도 찾기 함수입니다.
예: =INDEX(A1:A10, 3) 👉 A1부터 A10 중에서 3번째 값을 가져와라.
1-2. MATCH 함수: 번호표 뽑기
MATCH는 값을 가져오지는 못합니다. 대신 그 값이 "몇 번째에 있는지(위치 번호)"를 알려줍니다.
예: =MATCH("홍길동", A1:A10, 0) 👉 "홍길동"이 A1:A10 중에서 5번째에 있네! (결과: 5)
2. 환상의 콤비: MATCH가 찾고, INDEX가 가져온다!
자, 이제 두 함수를 합체해 볼까요? 원리는 간단합니다.
- MATCH가 찾으려는 값이 몇 번째 줄에 있는지 알아냅니다. (예: 5번째)
- 그 숫자(5)를 INDEX에게 넘겨줍니다.
- INDEX는 결과 범위의 5번째 값을 딱 집어옵니다.
=INDEX(결과범위, MATCH(찾을값, 기준범위, 0))
이 공식은 문장으로 외우는 게 제일 빠릅니다.
"INDEX야, [결과범위]에서 가져와. 몇 번 꺼? MATCH가 [기준범위]에서 [찾을 값]을 찾은 그 위치의 것을!"
3. 실전 예제: VLOOKUP이 못하는 '왼쪽 찾기' 도전
VLOOKUP이 절대 해결하지 못하는 상황을 해결해 보겠습니다.
상황: B열에 '이름', A열에 '사번'이 있습니다.
우리는 이름(B열)을 보고 왼쪽의 사번(A열)을 찾아와야 합니다.
| A열 (사번) | B열 (이름) | D1 (찾을 이름) | E1 (결과 사번) |
| 1001 | 김철수 | 이영희 | ? |
| 1002 | 이영희 |
따라 하기 (Step by Step):
1. MATCH로 위치 찾기:
먼저 '이영희'가 이름 목록(B열)에서 몇 번째인지 찾습니다.MATCH("이영희", B:B, 0) 👉 결과: 2 (두 번째에 있군요)
2. INDEX로 값 가져오기:
사번 목록(A열)에서 2번째 값을 가져오라고 명령합니다.INDEX(A:A, 2)
3. 합체하기:2라는 숫자 자리에 MATCH 수식을 그대로 끼워 넣습니다.
=INDEX(A:A, MATCH("이영희", B:B, 0))
해석: A열(사번)에서 가져와라. B열(이름)에서 "이영희"가 있는 그 위치(행)에 해당하는 값을!
4. VLOOKUP vs INDEX & MATCH 비교 분석
왜 고수들이 굳이 수식이 더 긴 INDEX & MATCH를 선호할까요?
| 구분 | VLOOKUP | INDEX & MATCH |
|---|---|---|
| 방향 제약 | 오른쪽으로만 검색 가능 | 좌우상하 제약 없음 |
| 열 삽입 시 | 수식이 꼬여서 에러 발생 (3번째 열이 4번째가 됨) |
중간에 열을 추가해도 안전하게 유지됨 |
| 속도 | 데이터가 많으면 느림 | 필요한 열만 참조하므로 빠름 |
5. 마치며: 이제 XLOOKUP으로 갈아타야 할까?
혹시 XLOOKUP 함수를 들어보셨나요? 엑셀 2019 이상 버전부터는 INDEX & MATCH의 기능을 합쳐놓은 XLOOKUP이 등장했습니다. 사용법도 훨씬 간단합니다.
하지만 아직 실무에서는 INDEX & MATCH가 필수입니다. 왜냐하면 회사의 공용 PC나 거래처의 엑셀 버전이 낮을 확률이 높기 때문입니다. 내 컴퓨터에서 XLOOKUP으로 멋지게 짰는데, 상사 컴퓨터에서 #NAME? 에러가 뜬다면 낭패겠죠?
오늘 배운 INDEX & MATCH 조합은 엑셀 역사상 가장 강력하고 안정적인 검색 스킬입니다. 이 수식을 자유자재로 다룬다면, 어떤 복잡한 데이터베이스도 두려울 것이 없습니다.
지금까지 데이터를 찾고, 분류하고, 가져오는 함수들을 모두 마스터하셨습니다. 다음 시간에는 이 딱딱한 숫자들을 '시각적인 정보'로 바꿔주는 [조건부 서식: 데이터 막대와 색조]에 대해 알아보겠습니다. 보고서의 퀄리티가 확 달라지는 마법을 기대해 주세요!
도움이 되셨다면 공감과 댓글 부탁드립니다. 여러분의 칼퇴를 응원합니다!