VLOOKUP은 표의 첫 번째 열에서 값을 찾고 오른쪽으로 몇 번째 열의 값을 가져오는 함수이고, XLOOKUP은 찾을 범위와 가져올 범위를 따로 지정하는 개선된 버전입니다. 눈에 띄는 차이는 XLOOKUP이 기본값으로 정확히 일치하는 값을 찾고, 가져올 열이 왼쪽에 있어도 쓸 수 있다는 점인데요. 다만 XLOOKUP은 Excel 2016과 Excel 2019에서는 쓸 수 없어서, 파일을 함께 쓰는 사람의 엑셀 버전도 봐야 합니다.
"캠페인 코드로 매체명을 붙여야 하는데, VLOOKUP을 써야 할까요, XLOOKUP을 써야 할까요?" 광고 리포트나 주문 목록을 정리하다 보면 한 시트의 코드를 기준으로 다른 시트의 값을 끌어와야 할 때가 많죠. 오랫동안 이 일은 VLOOKUP의 몫이었는데요, Microsoft는 이제 VLOOKUP 도움말 맨 위에서 XLOOKUP을 먼저 써 보라고 권하고 있습니다. 그렇다면 VLOOKUP은 이제 쓰지 않아도 되는 걸까요? 이번 글에서는 두 함수가 무엇이 다르고, 언제 무엇을 쓰면 좋은지 공식 도움말 기준으로 정리해 보겠습니다.
1. VLOOKUP은 어떻게 값을 찾을까요?
VLOOKUP의 구문은 =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])입니다.
도움말은 이 구조를 「찾으려는 값, 찾을 위치, 반환할 값이 들어 있는 열 번호, 근사값 또는 정확히 일치(1/TRUE 또는 0/FALSE)」로 풀어 설명하는데요.
- lookup_value: 찾으려는 값입니다.
table_array로 지정한 범위의 첫 번째 열에 있어야 합니다. - table_array: 찾을 값과 반환할 값이 모두 들어 있는 범위입니다.
- col_index_num: 범위의 가장 왼쪽 열을 1로 세었을 때, 반환할 값이 있는 열 번호입니다.
- range_lookup: TRUE면 근사값, FALSE면 정확히 일치하는 값을 찾습니다.
VLOOKUP은 극장 좌석표를 보는 방식과 비슷한데요. 맨 왼쪽의 줄 이름에서 원하는 줄을 찾은 다음, 오른쪽으로 몇 번째 자리인지 세어 가는 식이죠.
그래서 도움말도 VLOOKUP의 비결은 찾는 값이 반환할 값의 왼쪽에 오도록 데이터를 배치하는 것이라고 설명합니다.
예를 들어 A열에 캠페인 코드, C열에 매체명이 있다면 =VLOOKUP(F2, A2:C100, 3, FALSE)처럼 쓰는데요, 여기서 3은 A열부터 세어 세 번째 열이라는 뜻입니다.
열 번호를 숫자로 직접 적는 구조라서, 표에 열을 넣거나 빼고 나면 이 숫자가 여전히 맞는지 한 번 더 보시는 게 좋습니다.
2. VLOOKUP을 쓸 때 가장 많이 헷갈리는 점은 무엇일까요?
바로 네 번째 인수를 비워 두면 근사값으로 찾는다는 점입니다.
도움말에 따르면 range_lookup을 지정하지 않으면 기본값은 TRUE, 즉 근사값 일치이고, 이때는 첫 번째 열이 숫자순이나 사전순으로 정렬되어 있다고 가정하고 가장 가까운 값을 찾습니다.
첫 번째 열이 정렬되어 있지 않으면 예상과 다른 값이 돌아올 수 있기 때문에, 코드나 이름처럼 딱 맞는 값을 찾을 때는 마지막에 FALSE를 꼭 넣어야 하죠.
도움말의 「일반적인 문제」 표에는 이런 오류도 정리되어 있습니다.
| 오류 | 원인 (공식 도움말) |
|---|---|
| #N/A | FALSE로 찾았는데 정확히 일치하는 값이 없을 때, 또는 TRUE로 찾았는데 찾는 값이 첫 번째 열의 가장 작은 값보다 작을 때 |
| #REF! | col_index_num이 범위의 열 개수보다 클 때 |
| #NAME? | 보통 수식에서 텍스트에 따옴표가 빠졌을 때 |
또 하나 알아 두실 점은 VLOOKUP이 왼쪽에서 오른쪽으로만 찾는다는 것입니다. VLOOKUP·INDEX·MATCH 도움말은 찾는 값이 가장 왼쪽 열에 없다면 VLOOKUP 대신 INDEX와 MATCH를 함께 쓰라고 안내하는데요. 그렇다면 XLOOKUP은 이 문제를 어떻게 풀었을까요?
3. XLOOKUP은 VLOOKUP과 무엇이 다를까요?
XLOOKUP 도움말의 구문은 =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])입니다.
도움말이 짚는 핵심은, XLOOKUP은 조회 배열과 반환 배열을 따로 받는 반면 VLOOKUP은 하나의 표 범위와 열 번호를 쓴다는 점인데요.
앞의 예시를 XLOOKUP으로 바꾸면 =XLOOKUP(F2, A2:A100, C2:C100)이 되고, 열 번호를 셀 필요가 없어집니다.
| 비교 항목 | VLOOKUP | XLOOKUP |
|---|---|---|
| 기본 일치 방식 | 근사값(TRUE) | 정확히 일치(0) |
| 찾는 방향 | 첫 번째 열에서 오른쪽으로만 | 반환할 열이 어느 쪽에 있어도 가능 |
| 반환할 위치 지정 | 열 번호(숫자) | 반환할 범위 |
| 못 찾았을 때 | #N/A | if_not_found에 적은 값, 비워 두면 #N/A |
| 여러 열 한 번에 반환 | 지원 안 함 | 가능 (예: 이름과 부서를 한 수식으로) |
| 검색 순서 지정 | 인수 없음 | 처음부터(기본값) 또는 마지막 항목부터 역방향 |
선택 인수도 조금 더 풍부합니다.
- if_not_found: 일치하는 값이 없을 때 보여 줄 값입니다. 예를 들어
"미등록"이라고 적어 두면 #N/A 대신 그 글자가 나옵니다. - match_mode: 0은 정확히 일치(기본값), -1은 없으면 다음으로 작은 항목, 1은 없으면 다음으로 큰 항목, 2는
*,?,~를 쓰는 와일드카드 일치입니다. - search_mode: 1은 첫 번째 항목부터(기본값), -1은 마지막 항목부터 거꾸로 찾습니다. 2와 -2는 정렬된 범위를 전제로 한 이진 검색인데요, 정렬되어 있지 않으면 잘못된 결과가 나온다고 도움말이 적고 있습니다.
도움말 예제에는 XLOOKUP을 중첩해 세로와 가로를 동시에 찾는 방법도 나오는데, INDEX와 MATCH를 함께 쓰는 것과 비슷하다고 설명합니다. 가로 방향으로 찾는 HLOOKUP도 XLOOKUP으로 대신할 수 있다고 하고요.
※ 참고 사항: XLOOKUP을 쓸 수 없는 엑셀 버전이 있나요?
있습니다. XLOOKUP 도움말은 Excel 2016과 Excel 2019에서는 XLOOKUP을 사용할 수 없다고 명시하고 있는데요. Excel 함수 목록(사전순)에도 XLOOKUP 옆에 「2021」이라는 버전 표시가 붙어 있고, 이 표시는 해당 버전에서 도입되어 이전 버전에서는 쓸 수 없다는 뜻이라고 설명합니다. 도움말의 「적용 대상」 목록에는 Excel 2016·2019도 함께 적혀 있어 헷갈릴 수 있지만, 본문 안내는 두 버전에서 쓸 수 없다는 것입니다.
도움말은 최신 버전에서 누군가 만든 XLOOKUP 수식이 든 통합 문서를 Excel 2016이나 2019에서 쓰게 되는 상황이 생길 수 있다고도 적고 있는데요. 그래서 XLOOKUP을 넣은 파일을 동료에게 보낼 때는 받는 쪽의 버전을 먼저 확인하시는 게 좋습니다. 반면 VLOOKUP은 도움말 적용 대상에 Excel 2016부터 Microsoft 365까지 모두 올라 있습니다. 파일을 받는 사람의 엑셀 버전을 모른다면, 이 점이 아직 VLOOKUP을 쓰는 가장 현실적인 이유라고 저는 생각하는데요.
4. 그렇다면 언제 무엇을 쓰면 좋을까요?
공식 도움말 내용을 바탕으로 제 기준을 정리하면 이렇습니다.
- XLOOKUP을 먼저 고려할 때: 나와 동료 모두 Microsoft 365나 Excel 2021 이후 버전을 쓸 때, 찾는 값보다 왼쪽에 있는 열을 가져와야 할 때, 여러 열을 한 번에 가져오고 싶을 때, 못 찾은 값에 「미등록」 같은 표시를 남기고 싶을 때
- VLOOKUP을 유지할 때: Excel 2016·2019 사용자와 파일을 주고받을 때, 이미 VLOOKUP으로 만들어 둔 보고서가 잘 돌아가고 있을 때
- VLOOKUP을 쓴다면: 네 번째 인수에
FALSE를 넣고, 찾는 값이 범위의 첫 번째 열에 오도록 표를 배치합니다. 왼쪽 열을 가져와야 한다면 INDEX와 MATCH 조합을 씁니다.
정리하면 두 함수는 같은 일을 하지만, Microsoft는 XLOOKUP을 어느 방향으로나 찾고 기본값으로 정확히 일치하는 값을 돌려주는 VLOOKUP의 개선된 버전이라고 소개합니다. 근사값 기본값과 열 번호, 왼쪽 열 제한을 신경 쓰지 않아도 된다는 점이 실무에서 느끼는 차이일 텐데요. 찾아 붙인 결과를 여러 기준으로 요약하고 싶다면 피벗테이블과 함께 쓰시는 것도 권합니다. 저도 리포트를 정리하면서 두 함수를 상황에 맞게 나눠 쓰는 연습을 해 보려고 하니, 자세한 인수 설명은 공식 도움말을 참고해 주세요.
자주 묻는 질문
- VLOOKUP XLOOKUP 차이를 한 줄로 말하면 무엇인가요?
- VLOOKUP은 범위의 첫 번째 열에서 찾고 열 번호로 오른쪽 값을 가져오며 기본값이 근사값 일치입니다. XLOOKUP은 찾을 범위와 가져올 범위를 따로 지정해 어느 방향으로나 찾을 수 있고, 기본값이 정확히 일치입니다. 못 찾았을 때 보여 줄 값도 인수로 정할 수 있습니다.
- 엑셀 2016이나 2019에서도 XLOOKUP을 쓸 수 있나요?
- Microsoft XLOOKUP 도움말은 Excel 2016과 Excel 2019에서 XLOOKUP을 사용할 수 없다고 안내합니다. Excel 함수 목록에서도 XLOOKUP은 Excel 2021에서 도입된 함수로 표시되어 있습니다. 이 버전을 쓰는 사람과 파일을 주고받는다면 VLOOKUP이나 INDEX와 MATCH 조합을 쓰는 편이 안전합니다.
- VLOOKUP 결과가 엉뚱한 값으로 나오는 이유는 무엇인가요?
- 네 번째 인수(range_lookup)를 비워 두면 근사값 일치(TRUE)로 동작하고, 이때 첫 번째 열이 정렬되어 있지 않으면 예상과 다른 값이 반환될 수 있습니다. 정확한 값을 찾으려면 네 번째 인수에 FALSE를 넣으시면 됩니다.
- 엑셀 XLOOKUP 사용법에서 기본 인수는 몇 개인가요?
- 필수 인수는 찾을 값(lookup_value), 찾을 범위(lookup_array), 가져올 범위(return_array) 세 개입니다. 여기에 못 찾았을 때 값(if_not_found), 일치 방식(match_mode), 검색 순서(search_mode)를 선택으로 더할 수 있습니다.
관련 글 더 보기
엑셀로 데이터를 정리할 때 함께 보면 좋은 글들입니다.
- 엑셀 피벗테이블은 무엇이고, 어떨 때 쓰는 기능일까? - 찾아 붙인 데이터를 여러 관점으로 요약하는 방법
- 엑셀 데이터 한글 텍스트 깨질 때 변환하는 법 - 내려받은 데이터를 엑셀로 열 때 한글이 깨지는 문제 해결