엑셀로 일하다 보면 “이 표에 있는 거래처 코드로 저 표에서 단가 좀 끌어와” 같은 작업을 한 달에도 몇 번씩 한다. 대부분 VLOOKUP으로 시작한다. 그러다 어느 날 찾을 값이 표 맨 왼쪽이 아니라 가운데 열에 있어서 안 되거나, #N/A가 잔뜩 떠서 멘붕이 오거나, 회사 PC 엑셀에는 XLOOKUP이 아예 없다는 걸 알게 된다.
세 함수를 다 외울 필요는 없다. 다만 “어떤 상황에 뭘 꺼내 쓰는지”는 알아두면 평생 써먹는다. 그 기준을 정리해 본다.
30초 결론: 상황별로 이렇게 고른다
- 엑셀 365 또는 2021 이상을 쓴다 → 그냥 XLOOKUP. 고민할 거 없다.
- 회사 PC가 엑셀 2016이나 2019다 → XLOOKUP이 없으니 INDEX+MATCH.
- 버전 상관없이 단순하게, 찾을 값이 표 맨 왼쪽에 있고 오른쪽 값만 가져오면 된다 → VLOOKUP도 충분하다.
- 찾을 값이 왼쪽 열이 아니다 / 행과 열을 동시에 매칭해야 한다 → XLOOKUP 또는 INDEX+MATCH (VLOOKUP은 탈락).
XLOOKUP은 2019년 말부터 Microsoft 365에 들어왔고, 그 뒤 영구 라이선스 버전인 엑셀 2021·2024와 웹용 엑셀에도 추가됐다. 엑셀 2016·2019에는 끝내 안 들어왔다. 그래서 “내 집 노트북에서는 되는데 회사에서는 #NAME? 오류가 뜬다”는 일이 생긴다.
VLOOKUP 기본기 — 인수 네 개만 이해하면 끝
=VLOOKUP(찾을값, 범위, 열번호, 일치옵션)거래처 코드표가 A2:C100에 있고 (A열=코드, B열=거래처명, C열=단가), E2 셀에 코드를 입력하면 단가를 가져오는 식은 이렇다.
=VLOOKUP(E2, $A$2:$C$100, 3, FALSE)네 번째 인수 FALSE(또는 0)가 핵심이다. 이걸 빼거나 TRUE로 두면 “근사값 일치” 모드가 되는데, 표가 정렬돼 있지 않으면 엉뚱한 값을 멀쩡한 얼굴로 돌려준다. 단가표·코드표처럼 정확히 일치해야 하는 경우에는 무조건 FALSE를 쓴다. 근사값 일치는 점수→등급 변환 같은 구간 매칭에만 쓴다.
범위에 $를 거는 것도 잊지 말자. 식을 아래로 드래그해서 복사할 때 범위가 같이 밀려 내려가면 아래쪽 행은 표 일부만 보게 돼서 #N/A가 난다. 가장 흔한 실수 세 가지를 꼽으면 — ① FALSE 안 씀 ② 범위 절대참조 안 함 ③ 열 번호를 손가락으로 세다가 한 칸 틀림. 세 번째는 표에 열을 하나 추가하는 순간 또 깨진다.
VLOOKUP이 못 하는 네 가지
| 한계 | 언제 터지나 |
|---|---|
| 왼쪽 조회 불가 | 찾을 값이 가져올 값보다 오른쪽 열에 있을 때. VLOOKUP은 무조건 첫 열에서 찾아서 오른쪽 값만 반환 |
| 열 추가·삭제 시 깨짐 | 표 중간에 열을 끼워 넣으면 열 번호 3이 가리키던 자리가 바뀜. 식은 그대로라 조용히 틀린 값 |
| 다중 조건 불가 | ”제품명 + 옵션” 두 개를 동시에 키로 쓰고 싶을 때 기본 문법으로는 안 됨 (보조 열을 만들어 두 값을 이어 붙이는 꼼수 필요) |
| 대용량에서 느림 | 수만 행 × 여러 식이 겹치면 체감 속도 저하. INDEX+MATCH나 XLOOKUP이 보통 더 가벼움 |
왼쪽 조회는 정말 자주 막힌다. 받은 파일이 “거래처명 - 코드 - 단가” 순으로 돼 있는데 거래처명으로 찾아야 하는 식이면 VLOOKUP으로는 손을 못 댄다. 이때가 다른 함수로 넘어갈 타이밍이다.
XLOOKUP — VLOOKUP의 거의 모든 불편을 없앤 버전
=XLOOKUP(찾을값, 찾을범위, 반환범위, [없을때값], [일치모드], [검색모드])방금 그 “거래처명 - 코드 - 단가” 파일에서 거래처명(B열)으로 단가(D열)를 찾는다면:
=XLOOKUP(F2, $B$2:$B$100, $D$2:$D$100, "코드없음")장점이 한눈에 보인다. 찾을 범위와 반환 범위를 따로 지정하니 방향 제약이 없다. 왼쪽이든 오른쪽이든 상관없다. 기본 일치 모드가 “정확히 일치”라 VLOOKUP처럼 FALSE를 까먹을 일이 없다. 네 번째 인수 [없을때값]에 텍스트를 넣으면 못 찾았을 때 #N/A 대신 그 텍스트가 뜨니까 IFERROR로 감쌀 필요도 없다.
여섯 번째 인수 [검색모드]에 -1을 넣으면 아래에서 위로 검색한다. 같은 키가 여러 번 나오는 로그성 데이터에서 “가장 마지막 값”을 뽑을 때 쓴다. 가로로 정리된 표(HLOOKUP이 하던 일)도 찾을 범위·반환 범위를 행 방향으로 지정하면 그대로 된다. HLOOKUP을 따로 외울 이유가 없어진 셈이다.
단점은 하나뿐이다. 엑셀 2016·2019, 그리고 그 버전을 쓰는 사람이 받을 파일에서는 작동하지 않는다. 협업 상대의 환경을 모를 때는 다음 카드를 쓴다.
INDEX+MATCH — 버전 안 가리는 만능 조합
=INDEX(반환범위, MATCH(찾을값, 찾을범위, 0))작동 원리는 두 단계다. MATCH(찾을값, 찾을범위, 0)이 “그 값이 범위에서 몇 번째에 있는지” 위치 번호를 돌려주고, INDEX(반환범위, 그 번호)가 반환 범위에서 그 위치의 값을 꺼낸다. MATCH의 세 번째 인수 0이 “정확히 일치”다. 역시 까먹지 말 것.
거래처명(B열)으로 단가(D열) 찾기:
=INDEX($D$2:$D$100, MATCH(F2, $B$2:$B$100, 0))방향 제약 없고, 표 중간에 열을 추가해도 범위 참조라 잘 안 깨지고, 엑셀 95 시절부터 모든 버전에서 된다. 행과 열을 동시에 매칭하는 양방향 조회도 가능하다 — 예를 들어 “사번이 세로로, 월이 가로로” 정리된 표에서 특정 사번·특정 월의 값을 찾을 때:
=INDEX(데이터범위, MATCH(사번, 사번열, 0), MATCH("3월", 월행, 0))단점은 식이 길고 처음 보면 헷갈린다는 것. 팀원이 유지보수할 일이 많은 시트라면 가독성에서 XLOOKUP에 밀린다.
세 함수 한 표로
| 항목 | VLOOKUP | XLOOKUP | INDEX+MATCH |
|---|---|---|---|
| 지원 버전 | 거의 전 버전 | 365 / 2021 / 2024 / 웹 | 거의 전 버전 |
| 조회 방향 | 오른쪽만 | 자유 | 자유 |
| 양방향(행+열) | 불가 | 가능 | 가능 |
| 다중 조건 | 보조 열 필요 | 보조 열 또는 배열 | 배열 수식으로 가능 |
| 못 찾을 때 처리 | IFERROR 필요 | 인수로 내장 | IFERROR 필요 |
| 열 추가에 강한가 | 약함(열번호 깨짐) | 강함 | 강함 |
| 식 길이·가독성 | 짧음 | 짧음 | 김 |
| 추천 상황 | 단순한 오른쪽 조회 | 최신 버전 전반 | 구버전·협업 파일 |
#N/A, #REF! — 오류 진단 빠른 참조
- #N/A: 찾는 값이 진짜 없거나, 있어 보이지만 형식이 다른 경우. 한쪽은 텍스트 “1001”, 다른 쪽은 숫자 1001이면 다른 값으로 본다. 앞뒤에 보이지 않는 공백이 붙어 있어도 마찬가지 —
TRIM으로 공백 제거하거나,VALUE/TEXT로 형식을 맞춘다. - #REF!: VLOOKUP 열 번호가 범위의 열 수보다 클 때, 또는 참조하던 범위·시트를 삭제했을 때.
- #VALUE!: 인수 위치가 틀렸거나(찾을값 자리에 범위를 넣는 등) 형식이 안 맞을 때.
- #NAME?: 함수 이름 오타거나, 엑셀 2016에서 XLOOKUP을 썼을 때. 후자라면 INDEX+MATCH로 바꿔야 한다.
값이 없을 수도 있는 경우를 깔끔하게 처리하려면:
=IFNA(VLOOKUP(E2, $A$2:$C$100, 3, FALSE), "조회 안 됨")IFERROR는 모든 오류를 다 잡아 주지만, 그래서 식 자체의 실수까지 가려 버린다. “값이 없는 경우”만 처리하고 싶으면 IFNA가 더 안전하다.
실무에서 자주 나오는 케이스 몇 개
- 점수 → 등급 변환: 0·60·70·80·90 같은 구간 시작점을 정렬해 두고 VLOOKUP의 네 번째 인수를
TRUE로. 근사값 일치가 제값을 하는 거의 유일한 경우다. - 두 조건으로 찾기: 보조 열에
=제품명&"|"&옵션을 만들어 두고, 찾을 때도XLOOKUP(F2&"|"&G2, 보조열, 반환열)로 이어 붙인 키를 쓴다. - 두 시트 대조해 누락 찾기: A시트 키 옆에
=IFNA(XLOOKUP(키, B시트키, B시트키), "B에 없음")— “B에 없음”이 뜨는 행이 누락분이다. - 부분 일치로 회사명 찾기: XLOOKUP의 다섯 번째 인수
[일치모드]를2로 두면 와일드카드(*,?)가 먹는다."삼성*"같은 식으로. - 구글 시트에서는?: VLOOKUP·INDEX·MATCH 동일하게 작동하고, XLOOKUP도 지원한다.
ARRAYFORMULA로 한 열 전체를 한 번에 처리하는 게 구글 시트만의 무기다.
다음에 해볼 것
지금 쓰고 있는 시트를 하나 열어서, VLOOKUP이 들어간 식이 있으면 그게 ① FALSE를 쓰고 있는지 ② 범위에 $가 걸려 있는지 두 가지만 확인해 보자. 둘 중 하나라도 빠져 있으면 언젠가 조용히 틀린 값을 뱉을 폭탄이다. 그리고 회사 PC의 빈 셀에 =XLOOKUP(까지 쳐 보면, 자동완성이 뜨는지 안 뜨는지로 INDEX+MATCH를 외워둬야 할지 말지가 바로 갈린다.