엑셀 VLOOKUP vs XLOOKUP 함수 어떻게 사용할까? 차이점·예제·오류 해결 총정리
엑셀에서 상품코드로 가격을 찾거나 사원번호로 이름을 불러올 때 자주 사용하는 함수가 VLOOKUP과 XLOOKUP입니다. 두 함수 모두 기준값에 맞는 데이터를 찾아주지만, 검색 방향과 수식 작성 방법, 오류 처리 방식에 차이가 있습니다.
이번 글에서는 직접 입력해 볼 수 있는 상품표를 이용해 두 함수의 사용법을 비교하고, 실무에서 자주 발생하는 오류 해결 방법까지 정리합니다.
- VLOOKUP은 지정한 표의 첫 번째 열에서 값을 찾고, 오른쪽 열의 결과를 가져옵니다.
- XLOOKUP은 찾을 범위와 결과 범위를 따로 지정해 왼쪽 데이터도 쉽게 가져옵니다.
- 코드나 이름을 정확히 찾으려면 VLOOKUP 마지막 인수에 FALSE 또는 0을 입력합니다.
- XLOOKUP은 기본값이 정확히 일치하는 검색입니다.
- Excel 2016·2019에서는 XLOOKUP을 사용할 수 없습니다.
1. VLOOKUP과 XLOOKUP 차이점
| 비교 항목 | VLOOKUP | XLOOKUP |
|---|---|---|
| 검색 기준 | 지정한 표의 첫 번째 열 | 별도로 지정한 검색 범위 |
| 결과 지정 | 표 안에서 몇 번째 열인지 숫자로 입력 | 반환할 셀 범위를 직접 지정 |
| 검색 방향 | 기본 구조는 왼쪽에서 오른쪽 | 왼쪽·오른쪽 및 가로 검색 가능 |
| 일치 방식 기본값 | 근사값 검색 | 정확히 일치하는 값 검색 |
| 값이 없을 때 문구 | IFNA 등과 조합 | 함수 안에서 지정 가능 |
| 중복값 처리 | 위에서 처음 찾은 값 반환 | 기본은 첫 값, 옵션으로 마지막 값 검색 가능 |
| 여러 열 반환 | 일반적인 사용에서는 열별 수식 작성 | 하나의 수식으로 여러 열 반환 가능 |
새 파일을 만들고 사용하는 엑셀이 지원한다면 XLOOKUP이 편리합니다. 구버전 엑셀 사용자에게 전달해야 하는 파일이라면 VLOOKUP이나 INDEX·MATCH 조합을 고려할 수 있습니다.
2. 연습용 상품표 준비하기
빈 시트의 A1:D5에 아래 데이터를 입력하세요. 가격과 재고는 ‘원’, ‘개’를 붙이지 않고 숫자만 입력합니다.
| 행 | A열: 상품코드 | B열: 상품명 | C열: 판매가격 | D열: 재고 |
|---|---|---|---|---|
| 1 | 상품코드 | 상품명 | 판매가격 | 재고 |
| 2 | P001 | 생수 | 700 | 48 |
| 3 | P002 | 콜라 | 1500 | 24 |
| 4 | P003 | 이온음료 | 1800 | 18 |
| 5 | P004 | 에너지음료 | 2000 | 12 |
F2 셀에 P002를 입력하고, G2 셀에 조회 수식을 넣어보겠습니다. 아래 예제는 별도 안내가 없는 한 각각 독립적으로 입력해 결과를 확인하면 됩니다.
3. VLOOKUP 함수 사용법
① 기본 문법
=VLOOKUP(찾을값, 표범위, 반환할열번호, [일치방식])상품코드를 찾아 가격을 가져오는 수식은 다음과 같습니다.
=VLOOKUP(F2,$A$2:$D$5,3,FALSE)- F2: 찾을 상품코드인 P002가 들어 있는 셀입니다.
- $A$2:$D$5: 검색에 사용할 상품표입니다.
- 3: 지정한 표에서 세 번째 열인 판매가격을 가져옵니다.
- FALSE: 상품코드가 정확히 일치하는 행을 찾습니다.
조회 결과는 1500입니다. 상품명을 가져오려면 열 번호를 2로, 재고를 가져오려면 4로 바꾸면 됩니다.
② 열 번호는 시트 전체 기준이 아닙니다
반환할 열 번호는 선택한 표의 맨 왼쪽 열을 1로 계산합니다. A2:D5에서는 A열이 1번, B열이 2번, C열이 3번입니다.
예를 들어 검색 범위가 B2:D5라면 B열이 1번이 됩니다. 또한 VLOOKUP은 그 범위의 첫 열인 B열에서 검색하므로, 이 범위로는 A열의 상품코드를 찾을 수 없습니다.
③ 마지막 인수를 생략하지 않기
VLOOKUP의 마지막 인수를 생략하면 근사값 검색으로 작동합니다. 상품코드·사원번호처럼 정확한 대상을 찾아야 하는 업무에서는 FALSE 또는 0을 명시하세요. 근사값 검색을 의도했다면 첫 번째 열의 정렬 조건도 확인해야 합니다.
4. XLOOKUP 함수 사용법
① 기본 문법
=XLOOKUP(찾을값, 찾을범위, 반환범위, [없을때표시], [일치모드], [검색모드])같은 상품코드로 판매가격을 가져오는 수식입니다.
=XLOOKUP(F2,$A$2:$A$5,$C$2:$C$5,"미등록 상품")A열에서 F2의 상품코드를 찾아 같은 행의 C열 가격을 반환합니다. F2가 P002이면 1500, P999처럼 등록되지 않은 코드이면 미등록 상품을 표시합니다.
② 왼쪽에 있는 값 찾기
F2에 상품명인 콜라를 입력하고 아래 수식을 사용하면, B열에서 이름을 찾은 뒤 왼쪽 A열의 상품코드를 가져옵니다.
=XLOOKUP(F2,$B$2:$B$5,$A$2:$A$5,"상품 없음")결과는 P002입니다.
③ 상품명·가격·재고를 한 번에 가져오기
F2를 다시 P002로 바꾸고 G2에 다음 수식을 입력하세요.
=XLOOKUP(F2,$A$2:$A$5,$B$2:$D$5,"미등록 상품")G2:I2에 콜라 / 1500 / 24가 나란히 표시됩니다. 결과가 펼쳐질 H2:I2는 비워두세요.
5. 수식을 복사할 때 범위가 바뀌지 않게 하는 방법
F2:F10에 조회할 상품코드를 입력하고 G열에 수식을 복사한다면, 상품표 범위에는 달러 기호($)를 붙여 고정합니다.
=VLOOKUP(F2,$A$2:$D$5,3,FALSE)
=XLOOKUP(F2,$A$2:$A$5,$C$2:$C$5,"미등록 상품")아래로 복사하면 F2는 F3, F4로 바뀌지만 상품표 범위는 그대로 유지됩니다. 이처럼 조회할 셀은 이동시키고 기준표는 고정하는 방식이 실무에서 자주 쓰입니다.
6. VLOOKUP·XLOOKUP 오류 해결 방법
| 증상 | 확인할 부분 | 해결 방향 |
|---|---|---|
| #N/A | 찾는 값 없음, 숫자·문자 형식 불일치, 공백 | 원본값과 조회값의 실제 내용 비교 |
| #REF! | VLOOKUP 열 번호가 표의 열 수를 초과하거나 참조가 삭제됨 | 표 범위와 열 번호, 삭제된 참조 점검 |
| #VALUE! | XLOOKUP 검색·반환 범위 크기 불일치 등 | 대응하는 행 수 또는 열 수 확인 |
| #NAME? | 함수명 오타, 따옴표 누락, 지원하지 않는 함수 | 수식 철자와 엑셀 버전 확인 |
| #SPILL! | 여러 결과가 펼쳐질 공간에 값이나 병합 셀 존재 | 출력 공간 확보, 병합 해제 |
| 오류 없이 다른 값 표시 | 근사값 설정, 잘못된 열 번호, 중복값 | 일치 방식과 반환 열, 중복 여부 확인 |
① 같은 숫자인데 #N/A가 나오는 경우
숫자 1001과 문자로 저장된 "1001"은 조회 과정에서 다르게 취급될 수 있습니다. 셀 서식을 ‘일반’으로 바꾸는 것만으로 기존 텍스트가 숫자로 변환되지는 않을 수 있습니다.
숫자로 관리해야 하는 항목은 오류 표시의 ‘숫자로 변환’이나 VALUE 함수를 활용하고, 상품코드처럼 문자로 관리할 항목은 양쪽 형식을 통일하세요. 특히 00123처럼 앞자리 0이 중요한 코드는 숫자로 변환하면 코드가 달라질 수 있습니다.
② 눈에 보이지 않는 공백이 있는 경우
조회값 또는 원본값에 앞뒤 공백이 있으면 일치하지 않을 수 있습니다. 보조 열에 아래 수식을 입력해 정리한 뒤, 정리된 열을 검색 기준으로 사용할 수 있습니다.
=TRIM(A2)웹에서 복사한 특수 공백은 TRIM만으로 제거되지 않을 수 있습니다. 일반적인 줄바꿈 없는 공백을 정리할 때는 다음 방식도 활용합니다.
=TRIM(SUBSTITUTE(A2,CHAR(160)," "))조회값만 정리하고 원본값의 공백을 그대로 두면 문제가 남을 수 있으므로 양쪽을 확인하세요.
③ XLOOKUP 범위 크기가 다른 경우
아래처럼 검색 범위는 4행인데 반환 범위는 3행이면 올바르게 대응하지 않습니다.
=XLOOKUP(F2,A2:A5,C2:C4)이 예제에서는 반환 범위를 C2:C5로 수정합니다. 행 수뿐 아니라 같은 상품이 같은 위치에 놓여 있는지도 확인해야 합니다.
④ 수식이 계산되지 않고 그대로 보이는 경우
셀 서식이 ‘텍스트’이거나 수식 앞에 작은따옴표가 붙어 있는지 확인하세요. 텍스트 서식이었다면 ‘일반’으로 변경한 뒤 셀 편집 상태에서 Enter를 눌러 다시 확정합니다. 시트 전체의 수식이 보인다면 ‘수식 표시’ 기능이 켜졌는지도 확인합니다.
7. 찾는 값이 없을 때 안내 문구 표시하기
VLOOKUP에서는 IFNA를 조합해 #N/A일 때만 원하는 문구를 표시할 수 있습니다.
=IFNA(VLOOKUP(F2,$A$2:$D$5,3,FALSE),"미등록 상품")다른 오류까지 모두 같은 문구로 바꾸려면 IFERROR를 사용할 수 있지만, 잘못된 참조나 수식 문제까지 가려질 수 있습니다.
=IFERROR(VLOOKUP(F2,$A$2:$D$5,3,FALSE),"확인 필요")XLOOKUP은 네 번째 인수에서 미검색 문구를 지정합니다. 다만 이 인수는 범위 오류 등 모든 오류를 처리하는 기능은 아닙니다.
8. 실무에서 자주 묻는 질문 FAQ
Q1. Excel 2019를 업데이트하면 XLOOKUP을 사용할 수 있나요?
Excel 2019 제품 자체에서는 지원하지 않습니다. XLOOKUP을 지원하는 Microsoft 365용 Excel, Excel 2021·2024 등의 환경이 필요합니다. 파일을 전달할 상대방의 버전도 확인하세요.
Q2. 중복된 상품코드가 있으면 어떻게 되나요?
두 함수의 기본 검색은 위에서 처음 일치한 항목을 반환합니다. 상품코드가 고유해야 하는 표라면 중복을 먼저 정리하는 것이 좋습니다.
Q3. XLOOKUP으로 마지막 일치값을 찾을 수 있나요?
검색 모드를 -1로 설정하면 아래에서 위로 검색합니다.
=XLOOKUP(F2,$A$2:$A$5,$C$2:$C$5,"미등록 상품",0,-1)이는 ‘가장 아래에 있는 일치값’을 찾는 방식입니다. 날짜가 최신인 기록을 뜻하는 것은 아니므로, 최신 기록을 가져오려면 데이터 정렬 상태도 확인해야 합니다.
Q4. 상품표가 늘어나면 어떻게 하나요?
현재 수식은 5행까지만 조회합니다. 상품을 추가하면 범위를 늘리거나, 원본을 엑셀 표로 만들어 구조적 참조를 사용하는 방식으로 관리하세요.
Q5. 함수 인수 사이의 쉼표가 입력되지 않으면 어떻게 하나요?
지역 설정에 따라 인수 구분자로 세미콜론(;)을 사용하는 환경이 있습니다. 예제 수식이 입력되지 않는다면 구분자 설정을 확인하세요.
9. 요약
- VLOOKUP: 첫 번째 열에서 검색하고 열 번호로 결과를 지정합니다.
- XLOOKUP: 검색 범위와 반환 범위를 분리해 지정합니다.
- 정확한 코드 조회: VLOOKUP은 FALSE, XLOOKUP은 기본값으로 시작하면 됩니다.
- 오류 점검: 원본값 존재 여부, 데이터 형식, 공백, 범위부터 확인하세요.
- 파일 공유: 상대방의 XLOOKUP 지원 여부를 확인하세요.
상품코드 하나로 가격을 가져오는 간단한 예제부터 연습하면 함수 구조를 이해하기 쉽습니다. 익숙해진 뒤 상품명·재고 조회와 여러 열 반환으로 확장해 보세요.
※ 참조사항
이 글의 상품명·가격·재고는 함수 설명을 위한 가상 데이터입니다. 예제는 쉼표를 인수 구분자로 사용하는 환경을 기준으로 작성했습니다. 엑셀 버전과 지역 설정에 따라 메뉴 및 오류 표시가 다를 수 있습니다.