상품 코드·사번처럼 정확히 같은 값을 찾아야 한다면 VLOOKUP의 네 번째 인수에 FALSE, XLOOKUP의 다섯 번째 인수에 0을 지정하세요. VLOOKUP은 이 인수를 생략하면 근사 일치가 기본이라 정렬되지 않은 표에서 그럴듯한 오답을 낼 수 있습니다. 정확 일치인데도 #N/A면 검색 범위, 텍스트와 숫자 자료형, 앞뒤 공백, 중복 키 순으로 확인합니다.
코드·이름을 정확히 찾는 기본 수식
조회값이 상품 코드, 사번, 주문번호, 우편번호처럼 정해진 키와 완전히 같아야 한다면 다음처럼 일치 옵션을 명시합니다. 예시는 A2의 코드를 F열에서 찾아 H열의 담당자를 반환합니다.
=VLOOKUP(A2,$F$2:$H$100,3,FALSE)
=XLOOKUP(A2,$F$2:$F$100,$H$2:$H$100,"코드 없음",0)VLOOKUP의 FALSE와 XLOOKUP의 마지막 0은 정확 일치를 뜻합니다. 수식을 아래로 채울 때 조회값 A2는 다음 행에 맞게 바뀌고, 표 범위의 달러 기호는 그대로 유지됩니다. 지역 설정에 따라 함수 인수 구분자가 쉼표 대신 세미콜론일 수 있습니다.
먼저 오류를 없애지 말고 수식의 의도부터 고정하세요. VLOOKUP 네 번째 인수를 비우면 근사 일치입니다. XLOOKUP은 정확 일치가 기본이지만, 팀원이 수식을 읽을 때 의도를 분명히 하려면 0을 적어 둘 수 있습니다.
작은 예제 표로 예상 결과를 먼저 확인
아래의 상품 표는 조회 방식 설명을 위한 합성 데이터입니다. A2에 P-102가 있다고 가정하면 이름은 무선 마우스, 담당자는 민서가 나와야 합니다.
| 코드(F열) | 상품명(G열) | 담당자(H열) |
|---|---|---|
| P-101 | 키보드 | 준호 |
| P-102 | 무선 마우스 | 민서 |
| P-103 | 모니터 받침대 | 수빈 |
=VLOOKUP(A2,$F$2:$H$4,2,FALSE)는 두 번째 열인 상품명을, =XLOOKUP(A2,$F$2:$F$4,$G$2:$G$4,"코드 없음",0)은 G열 상품명을 반환합니다. 아래 비교를 그대로 입력해 결과가 예상과 다른 함수만 진단하면 됩니다.
| 함수 | 찾는 범위 | 반환 위치 | 정확 일치 지정 |
|---|---|---|---|
| VLOOKUP | 한 표 범위 전체 | 범위의 왼쪽부터 센 열 번호 | 네 번째 인수 FALSE |
| XLOOKUP | 조회 키 열 | 별도 반환 열 | 다섯 번째 인수 0; 생략해도 기본은 정확 일치 |
값은 나오지만 틀리다면 VLOOKUP의 근사 일치부터 확인
#N/A가 아니라 비슷한 코드의 결과가 나온다면 수식이 근사 일치로 계산 중일 수 있습니다. VLOOKUP의 마지막 인수를 생략하거나 TRUE로 두면 조회 열이 오름차순으로 정렬됐다고 가정해 가장 가까운 값을 찾습니다. 코드 목록이 정렬되지 않았으면 다른 행의 이름이나 금액이 정상값처럼 표시될 수 있습니다.
=VLOOKUP(A2,$F$2:$H$100,3,FALSE)코드·이름을 대조하는 작업에는 위처럼 FALSE를 명시하세요. 반면 세율표처럼 구간 경계로 분류하는 경우에는 근사 일치가 목적일 수 있습니다. 그때는 경계값과 조회 열을 오름차순으로 두고, 경계 바로 아래·경계값·바로 위 값을 각각 시험해 구간 결과를 검산합니다. 표를 정렬하면 행이 다른 열과 분리될 수 있으니 관련 열 전체를 선택했는지 확인하세요.
XLOOKUP에서 비슷한 구간을 찾으려면 match_mode에 -1 또는 1을 명시해야 합니다. 검색 순서의 2 또는 -2는 정렬된 범위를 요구하는 이진 검색이며, 정렬 조건을 지키지 않으면 잘못된 결과가 날 수 있습니다. 일반 코드 조회라면 이 옵션을 넣지 말고 기본 순차 검색과 정확 일치를 사용하세요.
조회 열·반환 열과 복사 후 범위가 맞는지 점검
- VLOOKUP: 검색 키는 지정한 표 범위의 첫 번째 열에 있어야 합니다. 범위가
F:H라면 F열에서 키를 찾습니다. 키가 G열이고 결과가 F열에 있다면 VLOOKUP은 왼쪽 방향 조회를 할 수 없습니다. - 열 번호:
F:H안에서는 F가 1, G가 2, H가 3입니다. 시트의 실제 열 번호가 아니라 선택한 범위 안에서 다시 셉니다. - XLOOKUP: 검색 키 배열과 반환 배열의 첫 행·끝 행이 맞는지 확인합니다. 예를 들어
F2:F100을 검색하면서H2:H99를 반환 배열로 쓰면 범위 크기가 다릅니다. - 아래로 채우기: 고정 코드표 주소에
$가 빠져 두 번째 행부터 범위가 한 칸씩 내려가지 않는지 확인합니다. 계산된 두 번째 행 수식도 수식 입력줄에서 봅니다. - 새 데이터: 검색 범위의 마지막 행이 실제 새 항목까지 포함하는지 확인합니다. 표로 계속 행을 늘리는 파일이라면 Excel 표나 동적 범위를 사용하는 방식을 복사본에서 검토합니다.
키 열이 반환 열의 오른쪽에 있어야 하는 구성이라면 XLOOKUP으로 검색 열과 반환 열을 따로 지정할 수 있습니다. 또는 기존 파일에서 INDEX/MATCH를 유지할 수 있습니다. 어떤 함수를 택하든 범위가 실제 데이터와 일치하는지 검산은 필요합니다.
숫자처럼 보이는 코드와 텍스트 코드를 섞지 않기
화면에 모두 123으로 보여도 한쪽은 숫자 123, 다른 쪽은 문자 "123"일 수 있습니다. 00123과 123은 선행 0이 식별 의미의 일부라면 서로 다른 코드입니다. 표시 형식만 숫자나 텍스트로 바꾸는 것으로 저장된 값이 의도대로 변환됐다고 단정하지 마세요.
| 검사식 | 확인하는 것 | 다음 조치 |
|---|---|---|
=ISTEXT(A2) | A2가 문자열로 저장됐는지 | 검색값과 원본 키의 결과를 각각 검사 |
=ISNUMBER(F2) | F2가 실제 숫자인지 | 두 범위가 같은 자료형인지 대조 |
=LEN(A2) | 문자 수가 예상과 같은지 | 숨은 공백이나 앞자리 0 차이를 의심 |
금액·수량은 업무 기준에 맞춰 양쪽을 실제 숫자로 맞출 수 있습니다. 하지만 상품 코드·우편번호·계좌 식별자·사번은 숫자로 변환해 선행 0을 지우지 마세요. 원본을 복사한 보조 열에서 변환 결과를 시험하고, 코드 체계의 담당자와 값이 같은 식별자를 뜻하는지 확인한 뒤 적용합니다.
겉으로 안 보이는 공백은 양쪽 키를 같은 규칙으로 정리
웹·PDF·업무 시스템에서 복사한 값에는 일반 공백이나 줄바꿈 없는 공백(U+00A0)이 붙을 수 있습니다. 눈으로는 P-102처럼 보여도 문자열이 달라 조회가 실패합니다. 일반 앞뒤 공백에는 TRIM을 사용할 수 있지만, 줄바꿈 없는 공백은 먼저 바꿔야 할 수 있습니다.
=TRIM(SUBSTITUTE(F2,CHAR(160)," "))이 식은 보조 열에서 F2의 일반 공백과 흔한 NBSP를 정리하는 예입니다. 조회표의 키에만 정리식을 쓰고 A2 조회값을 그대로 두면 여전히 일치하지 않을 수 있으므로, 검색값과 원본 키 모두 동일한 규칙으로 정리한 뒤 정리된 두 열을 조회에 사용하세요. 원본 값을 한 번에 덮어쓰지 말고 결과를 몇 건 대조합니다. TRIM과 CLEAN이 모든 Unicode 공백을 없애는 것은 아닙니다.
중복 키는 오류 대신 그럴듯한 오답을 만들 수 있습니다
조회 열에 같은 코드가 두 번 있으면 기본 검색 순서에서 먼저 만난 행이 반환될 수 있습니다. 따라서 값이 정상적으로 보이더라도 실제로는 오래된 가격이나 다른 담당자 정보일 수 있습니다. 먼저 원본의 중복을 세어 후보를 찾습니다.
=COUNTIF($F$2:$F$100,F2)결과가 1보다 크면 같은 기준으로 중복 후보가 있다는 뜻입니다. 중복을 자동 삭제하기 전에 코드가 고유해야 하는 업무인지, 최신 행을 쓸지, 유효 기간을 같이 조회해야 하는지 정합니다. XLOOKUP은 검색 방향을 마지막 행부터 시작하도록 설정할 수 있지만, “마지막”이 최신이라는 보장은 데이터 정렬 규칙이 있을 때만 성립합니다.
오류 코드마다 확인할 원인은 다릅니다
| 결과 | 우선 확인할 항목 |
|---|---|
#N/A | 정확 일치 키 부재, 조회 범위 끝 행, 텍스트와 숫자, 공백·숨은 문자 |
| 값은 나오지만 다름 | VLOOKUP 근사 일치 기본값, 잘못된 열 번호, 중복 키의 첫 일치, 예전 표 범위 |
#REF! | VLOOKUP 반환 열 번호가 지정한 표의 열 수보다 큼, 삭제된 셀 참조 |
#VALUE! | 열 번호가 1보다 작거나 인수·범위 형식이 맞지 않는지 |
#NAME? 또는 함수 인식 문제 | 함수 이름·구분자, XLOOKUP을 지원하지 않는 Excel 버전인지 |
VLOOKUP은 선택한 첫 열에서만 검색하고 그보다 오른쪽의 반환 열을 고릅니다. 표 사이에 열을 삽입하면 숫자로 적은 열 인덱스가 예상과 달라질 수 있습니다. XLOOKUP은 검색 열과 반환 열이 분리되어 있어 열을 삽입해도 의도가 읽기 쉽습니다.
Microsoft 도움말은 XLOOKUP이 Excel 2016 및 Excel 2019에서 사용할 수 없다고 안내합니다. 다른 사람이 최신 Excel에서 만든 통합 문서에 XLOOKUP이 들어 있다면 구버전에서 함수 인식 오류가 날 수 있습니다. 해당 사용자에게 배포할 파일이라면 수신자의 실제 Excel 버전을 확인하고, 필요하면 정확 일치 VLOOKUP이나 INDEX/MATCH 호환 수식을 사용해 사본에서 결과를 비교하세요.
원인을 고친 다음 IFNA로 누락 메시지 표시
정확히 일치하는 코드가 실제로 없을 수 있는 업무라면 원인을 점검한 뒤 누락 메시지를 표시할 수 있습니다.
=IF(A2="","",IFNA(VLOOKUP(A2,$F$2:$H$100,3,FALSE),"코드 없음"))이 수식은 A2가 비어 있으면 빈칸, 일치하면 H열 값, 일치 키가 없으면 코드 없음을 표시합니다. XLOOKUP의 네 번째 인수로 이미 누락값을 지정했다면 바깥 IFNA는 중복일 수 있으므로 둘 중 하나만 쓰면 됩니다. 중요한 것은 오류를 숨기기 전에 실제 누락과 수식 오류를 구분하는 것입니다.
IFERROR는 #N/A뿐 아니라 #REF! 등 다른 오류도 한꺼번에 감쌀 수 있습니다. 표 범위나 반환 열이 잘못된 상태가 빈칸으로 숨지 않게 하려면, 조회 실패만 표시하는 IFNA를 우선 검토하세요.
조회 오류와 연결되는 Excel·Sheets 실무 가이드
자주 묻는 질문
VLOOKUP 마지막 인수를 생략하면 정확히 일치하나요?
아닙니다. 생략하거나 TRUE를 지정하면 근사 일치가 기본입니다. 코드·사번을 찾을 때는 네 번째 인수에 FALSE를 넣으세요.
XLOOKUP도 정확 일치 옵션을 적어야 하나요?
XLOOKUP은 정확 일치가 기본이라 다섯 번째 인수를 생략해도 됩니다. 정확 일치 의도를 분명히 하려면 0을 적을 수 있습니다.
VLOOKUP이 왼쪽에 있는 값을 반환할 수 있나요?
VLOOKUP은 지정한 범위의 첫 열에서 찾고, 그 열보다 오른쪽에서 결과를 반환합니다. 반대 방향이면 XLOOKUP이나 INDEX/MATCH를 검토하세요.
TRIM을 적용했는데 계속 #N/A가 나옵니다.
줄바꿈 없는 공백이나 다른 특수 문자가 남을 수 있습니다. 보조 열에서 SUBSTITUTE(...,CHAR(160)," ")와 TRIM을 함께 사용하고 조회값·원본값 양쪽에 같은 처리를 적용하세요.
코드 열에 VALUE를 써도 되나요?
수량처럼 숫자 의미를 가진 값인지 먼저 확인하세요. 선행 0이 식별자에 의미가 있으면 숫자로 바꾸지 말고 두 키를 텍스트로 일관되게 유지합니다.
값은 맞아 보이는데 어떤 경우에 틀린 결과가 나오나요?
근사 일치가 정렬되지 않은 키 표를 조회했거나, 중복 키의 첫 행을 반환했거나, 반환 열 번호가 바뀌었을 수 있습니다. 결과와 원본 키 행을 함께 대조하세요.
Microsoft 공식 자료
- VLOOKUP function — 표 범위 첫 열, 반환 열 번호, 생략 시 근사 일치
- XLOOKUP function — 기본 정확 일치, 조회·반환 배열, 지원 버전, 검색 모드
- How to correct a #N/A error — 숫자와 텍스트 자료형, 추가 공백 등 기본 진단
- TRIM function — 일반 공백 정리와 NBSP 주의
- Top ten ways to clean your data — SUBSTITUTE로 특수 공백을 일반 공백으로 바꾸는 방법
- IFNA function — #N/A 오류에만 대체값 표시
- Look up values with VLOOKUP, INDEX, or MATCH — 왼쪽에서 오른쪽으로 조회하는 VLOOKUP 구조와 대안