엑셀에서 VLOOKUP이 #N/A나 엉뚱한 값을 반환한다면 먼저 조회 범위의 첫 열, 정확히 일치 옵션, 데이터 형식을 확인하세요. XLOOKUP을 사용할 수 있는 Excel이라면 열 번호를 세지 않아도 되고 왼쪽 조회도 가능하므로 복잡한 조회 수식을 줄일 수 있습니다.
📌 핵심 5줄 요약
1. VLOOKUP은 조회값이 선택한 범위의 가장 왼쪽 열에 있어야 합니다.
2. 상품코드나 사번처럼 정확히 찾을 때는 VLOOKUP의 마지막 인수를 FALSE로 지정합니다.
3. XLOOKUP은 조회 범위와 반환 범위를 따로 지정해 왼쪽·오른쪽 어느 방향으로도 찾을 수 있습니다.
4. XLOOKUP은 기본적으로 정확히 일치하며, 찾지 못했을 때 표시할 문구도 지정할 수 있습니다.
5. 오래된 Excel과 파일을 공유해야 한다면 XLOOKUP 지원 여부를 먼저 확인해야 합니다. Microsoft는 VLOOKUP을 개선한 함수로 XLOOKUP을 안내하지만, Excel 2016과 Excel 2019에서는 XLOOKUP을 사용할 수 없다고 설명합니다.
📌 목차
01 | VLOOKUP과 XLOOKUP은 어떤 함수인가
02 | VLOOKUP 기본 구조 이해하기
03 | VLOOKUP #N/A 오류 원인
04 | VLOOKUP #VALUE 오류 확인하기
05 | VLOOKUP #REF 오류와 열 번호
06 | VLOOKUP이 엉뚱한 값을 반환할 때
07 | 숫자와 텍스트 형식 맞추기
08 | 공백과 숨은 문자 제거하기
09 | XLOOKUP 기본 구조 이해하기
10 | VLOOKUP과 XLOOKUP 차이
11 | 같은 데이터를 두 함수로 조회하기
12 | 실제 사례로 오류 수정하기
13 | 어떤 함수를 선택할지 판단하기
01 | VLOOKUP과 XLOOKUP은 어떤 함수인가
두 함수 모두 표나 범위에서 기준값을 찾은 뒤 같은 행의 다른 값을 반환하는 조회 함수입니다. 예를 들어 상품코드로 상품명이나 가격을 가져오거나, 사번으로 부서명을 불러올 때 사용할 수 있습니다.
VLOOKUP은 조회 범위의 첫 번째 열에서 값을 찾고, 지정한 열 번호의 값을 반환합니다. 반면 XLOOKUP은 조회 범위와 반환 범위를 별도로 지정하므로 데이터 배치에 대한 제약이 적습니다.
02 | VLOOKUP 기본 구조 이해하기
기본 구문은 다음과 같습니다.
=VLOOKUP(조회값, 조회범위, 반환열번호, 정확히일치여부)
예를 들어 A열에 상품코드, B열에 상품명, C열에 가격이 있다면 다음과 같이 작성할 수 있습니다.
=VLOOKUP(E2,A2:C100,3,FALSE)
이 수식은 E2의 값을 A2:A100에서 찾고, 같은 행의 세 번째 열인 C열 값을 반환합니다. VLOOKUP의 조회값은 table_array로 지정한 범위의 첫 번째 열에 있어야 하며, 마지막 인수를 생략하면 근사값 일치가 기본으로 적용됩니다.
03 | VLOOKUP #N/A 오류 원인
#N/A는 조회값을 찾지 못했다는 뜻입니다. 가장 흔한 원인은 실제로 값이 없거나, 숫자와 텍스트가 서로 다른 형식으로 저장되어 있거나, 셀에 앞뒤 공백이 포함된 경우입니다.
먼저 확인할 항목
조회값이 조회표의 첫 열에 있는지 확인합니다. VLOOKUP은 선택한 범위의 첫 번째 열에서만 검색하므로, 기준값이 B열에 있는데 범위를 A열부터 잡으면 원하는 결과가 나오지 않을 수 있습니다.
상품코드 1001이 한쪽에서는 숫자이고 다른 쪽에서는 문자형인 경우에도 일치하지 않습니다. 셀 왼쪽 정렬 여부만으로는 확정하기 어려우므로 데이터 형식을 직접 정리하는 것이 안전합니다.
04 | VLOOKUP #VALUE 오류 확인하기
#VALUE 오류는 인수의 형식이나 입력값이 잘못되었을 때 발생할 수 있습니다. 특히 반환 열 번호가 숫자가 아니거나, 범위와 수식 구조를 잘못 입력했는지 확인해야 합니다.
예를 들어 다음 수식에서 세 번째 인수는 조회 범위 안에서 반환할 열의 순번이어야 합니다.
=VLOOKUP(E2,A2:C100,"가격",FALSE)
위 수식처럼 열 번호 자리에 열 이름이나 문자형 값을 넣으면 문제가 생길 수 있습니다. 가격이 C열이라면 3을 입력해야 합니다.
05 | VLOOKUP #REF 오류와 열 번호
#REF 오류는 수식이 존재하지 않는 셀이나 범위를 참조할 때 나타납니다. 조회 범위가 A2:C100인데 반환 열 번호를 4로 입력하면 범위 안에 네 번째 열이 없기 때문에 오류가 발생합니다.
열을 삽입하거나 삭제한 뒤에도 수식이 깨질 수 있습니다. 수식을 클릭한 다음 조회 범위의 열 개수를 다시 세고, 반환 열 번호가 실제 범위 안에 있는지 확인하세요.
VLOOKUP의 열 번호는 워크시트 전체 기준이 아니라 선택한 조회 범위의 가장 왼쪽 열을 1로 계산합니다.
06 | VLOOKUP이 엉뚱한 값을 반환할 때
마지막 인수를 생략했거나 TRUE로 지정한 경우가 대표적인 원인입니다. VLOOKUP은 마지막 인수가 없으면 근사값 일치를 사용하므로, 정확히 같은 상품코드를 찾으려는 상황에서 예상하지 못한 값이 나올 수 있습니다.
일반적인 코드·사번·주문번호 조회에는 다음처럼 FALSE를 명시하는 것이 안전합니다.
=VLOOKUP(E2,A2:C100,3,FALSE)
TRUE 또는 근사값 일치를 사용하려면 첫 번째 열이 오름차순으로 정렬되어 있어야 합니다. 등급표나 구간별 수수료처럼 기준값 사이의 값을 찾는 경우에만 근사값 일치를 고려하세요.
07 | 숫자와 텍스트 형식 맞추기
화면에는 모두 1001로 보여도 한 셀은 숫자, 다른 셀은 텍스트일 수 있습니다. 이 경우 VLOOKUP이나 XLOOKUP이 값을 찾지 못할 수 있습니다.
실무에서 자주 쓰는 정리 방법
텍스트로 저장된 숫자를 숫자로 바꾸려면 빈 셀에 1을 입력해 복사한 뒤 해당 범위에 선택하여 붙여넣기에서 곱하기를 사용할 수 있습니다. 또는 =VALUE(A2)로 숫자 변환을 시도할 수 있습니다.
반대로 코드 앞의 0을 유지해야 한다면 숫자로 바꾸면 안 됩니다. 예를 들어 00125와 125가 다른 코드라면 양쪽 데이터를 모두 텍스트 형식으로 통일해야 합니다.
08 | 공백과 숨은 문자 제거하기
복사한 데이터에는 앞뒤 공백이나 줄 바꿈 같은 보이지 않는 문자가 포함될 수 있습니다. 이때는 =TRIM(A2)으로 불필요한 일반 공백을 제거하고, 외부 시스템에서 가져온 데이터라면 CLEAN 함수도 함께 검토합니다.
다만 TRIM은 모든 종류의 특수 공백을 완벽하게 제거하지 못할 수 있습니다. 조회가 계속 실패하면 원본 셀과 기준 셀의 길이를 LEN 함수로 비교해 숨은 문자가 있는지 확인하세요.
09 | XLOOKUP 기본 구조 이해하기
XLOOKUP의 기본 구문은 다음과 같습니다.
=XLOOKUP(조회값, 조회범위, 반환범위, 찾지 못했을 때 표시할 값)
예를 들어 상품코드가 A열, 가격이 C열에 있다면 다음처럼 작성할 수 있습니다.
=XLOOKUP(E2,A2:A100,C2:C100,"상품 없음")
네 번째 인수에 문구를 지정하면 일치하는 값이 없을 때 #N/A 대신 원하는 안내 문구를 표시할 수 있습니다. XLOOKUP은 기본적으로 정확히 일치하는 값을 찾으며, 조회 범위와 반환 범위의 방향도 VLOOKUP보다 유연합니다.
10 | VLOOKUP과 XLOOKUP 차이
| 구분 | VLOOKUP | XLOOKUP |
|---|---|---|
| 조회 방향 | 조회값이 범위의 첫 열에 있어야 함 | 왼쪽·오른쪽 조회 가능 |
| 반환 방식 | 열 번호를 직접 입력 | 반환할 범위를 직접 지정 |
| 정확히 일치 | FALSE를 직접 지정하는 것이 안전함 | 기본값이 정확히 일치 |
| 미일치 처리 | IFERROR 등을 별도로 사용 | 네 번째 인수로 문구 지정 가능 |
| 열 삽입 대응 | 열 번호 변경에 주의 | 반환 범위를 직접 지정해 관리가 쉬움 |
| 호환성 | 구버전 파일에 상대적으로 유리 | Excel 버전에 따라 지원 여부 확인 필요 |
Microsoft는 XLOOKUP이 어느 방향으로나 작동하고 기본적으로 정확한 일치 항목을 반환하는 개선된 함수라고 안내합니다. 다만 Excel 2016과 Excel 2019에서는 XLOOKUP 자체를 사용할 수 없으므로 공유 대상의 버전을 고려해야 합니다.
11 | 같은 데이터를 두 함수로 조회하기
다음과 같은 표를 가정해 보겠습니다.
A열은 상품코드, B열은 상품명, C열은 가격이며 E2 셀에 조회할 상품코드가 들어 있습니다.
VLOOKUP
=VLOOKUP(E2,A2:C100,3,FALSE)
XLOOKUP
=XLOOKUP(E2,A2:A100,C2:C100,"상품 없음")
두 수식은 같은 행의 가격을 반환하지만, XLOOKUP은 반환 범위가 C2:C100이라고 명확하게 드러납니다. 열이 추가되거나 표 구조가 바뀌는 업무에서는 XLOOKUP이 수식 내용을 확인하기 더 쉽습니다.
12 | 실제 사례로 오류 수정하기
예를 들어 사번으로 부서명을 찾는 수식이 다음과 같다고 가정하겠습니다.
=VLOOKUP(A2,직원표,3)
결과가 예상과 다르다면 첫째, 마지막 인수가 빠져 근사값 일치가 적용되었는지 확인합니다. 둘째, 직원표의 첫 열이 사번인지 확인합니다. 셋째, 부서명이 직원표의 세 번째 열에 실제로 있는지 확인합니다.
정확한 일치로 수정하면 다음과 같습니다.
=VLOOKUP(A2,직원표,3,FALSE)
XLOOKUP으로 바꾸면 다음과 같이 작성할 수 있습니다.
=XLOOKUP(A2,직원표[사번],직원표[부서],"사번 없음")
이 사례의 핵심은 함수를 바꾸는 것보다 조회값의 위치, 데이터 형식, 일치 방식부터 점검하는 것입니다.
13 | 어떤 함수를 선택할지 판단하기
새로 만드는 파일이고 사용자의 Excel이 XLOOKUP을 지원한다면 XLOOKUP을 우선 고려할 수 있습니다. 왼쪽 조회가 필요하거나, 반환할 열이 자주 바뀌거나, 미일치 메시지를 수식 안에서 관리해야 한다면 특히 유리합니다.
반대로 오래된 Excel을 사용하는 사람과 파일을 주고받거나, 기존 업무 양식이 VLOOKUP으로 통일되어 있다면 VLOOKUP을 유지하는 편이 안전할 수 있습니다. 함수의 우열보다 파일을 열 사람의 버전과 업무 호환성이 더 중요합니다.
복잡한 다중 조건 조회라면 XLOOKUP 하나로 해결하려 하기보다 보조열, FILTER, INDEX와 MATCH 등 다른 방법이 더 적합할 수 있습니다.
14 | 사용 전 체크리스트와 주의사항
조회 수식을 입력하기 전 다음 항목을 순서대로 확인하면 오류를 빠르게 줄일 수 있습니다.
- 조회값이 실제 조회 범위에 존재하는지 확인합니다.
- VLOOKUP이라면 조회값이 선택 범위의 첫 열에 있는지 확인합니다.
- 코드·사번 조회에서는 FALSE를 입력해 정확히 일치시킵니다.
- 조회값과 원본값의 숫자·텍스트 형식을 통일합니다.
- 앞뒤 공백과 줄 바꿈 등 숨은 문자를 제거합니다.
- VLOOKUP의 반환 열 번호가 선택 범위의 열 개수를 넘지 않는지 확인합니다.
- XLOOKUP을 사용할 경우 파일을 열 대상의 Excel 버전을 확인합니다.
IFERROR로 오류를 감추는 것만으로는 원인을 해결할 수 없습니다. 예를 들어 =IFERROR(VLOOKUP(...),"없음")은 화면을 깔끔하게 만들지만, 잘못된 코드나 데이터 형식 불일치까지 모두 ‘없음’으로 표시할 수 있으므로 원본 데이터 점검을 먼저 진행해야 합니다.
📖 자주하는 질문 Q&A
VLOOKUP에서 FALSE를 꼭 써야 하나요?
상품코드, 사번, 주문번호처럼 정확히 같은 값을 찾아야 한다면 FALSE를 쓰는 것이 안전합니다. 생략하면 근사값 일치가 기본으로 적용될 수 있어 예상하지 못한 결과가 나올 수 있습니다.
XLOOKUP이 VLOOKUP보다 항상 빠른가요?
조회 성능은 데이터 크기, 수식 개수, 통합 문서 구조 등에 따라 달라집니다. XLOOKUP의 핵심 장점은 단순히 속도보다 왼쪽 조회, 정확한 일치 기본값, 미일치 문구 지정, 반환 범위 직접 지정에 있습니다.
XLOOKUP에서 #N/A가 나오면 어떻게 하나요?
조회값이 실제로 존재하는지, 숫자와 텍스트 형식이 같은지, 공백이 포함되지 않았는지 확인하세요. 네 번째 인수에 "찾을 수 없음" 같은 문구를 지정하면 미일치 상황을 사용자에게 알기 쉽게 표시할 수 있습니다.
VLOOKUP을 XLOOKUP으로 무조건 바꿔야 하나요?
그럴 필요는 없습니다. 기존 파일의 호환성이 중요하거나 다른 사람이 구버전 Excel을 사용한다면 VLOOKUP을 유지하는 편이 낫습니다. 새 파일이고 지원 버전이 확인된 경우에는 XLOOKUP이 수식 관리에 더 편리할 수 있습니다.
📝 마무리
VLOOKUP 오류는 대부분 조회 범위의 첫 열, FALSE 지정 여부, 데이터 형식과 공백 문제에서 발생합니다. XLOOKUP은 왼쪽·오른쪽 조회와 미일치 문구 지정이 가능해 새 Excel 작업에 편리하지만, 파일 공유 전 지원 버전을 확인해야 합니다.