엑셀에서 상품코드, 사번 또는 주문번호를 조회하기 위해
VLOOKUP을 사용했는데 다음 오류가 표시될 수 있습니다.
수식이 요청한 조회 값을 찾지 못한 상태
조회 값이 기준표에 정말 없다면 #N/A가 표시되는 것이 정상입니다.
그러나 화면에서 같은 값이 분명히 보이는데도 오류가 발생한다면 숫자와 문자의
데이터 형식, 앞뒤 공백, 인쇄되지 않는 문자와 조회 범위를 확인해야 합니다.
오류를 바로 IFERROR로 감추기 전에 어느 단계에서 값이 일치하지
않는지 확인해야 합니다. 잘못된 범위나 열 번호까지 빈칸으로 숨기면 실제
오류가 남은 상태로 보고서가 사용될 수 있습니다.
이 글은 Microsoft의 VLOOKUP, #N/A 오류, TRIM, CLEAN, IFNA와 XLOOKUP 공식 문서를 기준으로 작성했습니다. 예제의 범위와 열 위치는 실제 통합 문서 구조에 맞게 변경해야 합니다.
 |
| VLOOKUP의 #N/A 오류는 조회 값이 없을 때뿐 아니라 숫자와 문자 형식이 다르거나 보이지 않는 공백이 포함된 경우에도 발생할 수 있습니다. 위 이미지는 조회 값과 기준표가 일치하지 않는 구조를 설명하기 위한 대표 개념 이미지입니다. |
먼저 사용할 VLOOKUP 기본 수식
아래 예제는 다음과 같은 데이터 구조를 사용합니다.
| 위치 | 내용 |
|---|---|
| A2 | 찾으려는 상품코드 |
| F2:F1000 | 기준표의 상품코드 |
| G2:G1000 | 상품명 |
| H2:H1000 | 단가 |
A2의 상품코드를 F열에서 찾아 H열의 단가를 반환하는 수식입니다.
=VLOOKUP(
A2,
$F$2:$H$1000,
3,
FALSE
)각 인수의 역할은 다음과 같습니다.
A2: 조회할 값$F$2:$H$1000: 조회 기준과 반환값을 포함한 범위3: 지정한 범위의 세 번째 열인 H열 값을 반환FALSE: 정확하게 같은 값을 검색
상품코드나 사번처럼 정확한 일치가 필요한 값은 네 번째 인수를 생략하지 않고
FALSE 또는 0으로 지정하는 편이 안전합니다.
#N/A 오류를 확인하는 순서
| 확인 순서 | 주요 원인 | 확인 방법 |
|---|---|---|
| 1 | 값이 실제로 없음 | 기준 열에서 조회 값을 직접 검색 |
| 2 | 근사 일치 사용 | 네 번째 인수가 FALSE인지 확인 |
| 3 | 숫자와 문자 형식 불일치 | ISNUMBER와 ISTEXT로 두 값을 비교 |
| 4 | 숨은 공백·제어 문자 | LEN, TRIM, CLEAN과 SUBSTITUTE 사용 |
| 5 | 조회 열 위치가 잘못됨 | 조회 값이 범위의 첫 번째 열인지 확인 |
| 6 | 복사하면서 범위가 이동함 | 기준표 범위에 절대 참조 적용 |
1. 조회 값이 기준표에 실제로 있는지 확인합니다
FALSE를 사용하는 VLOOKUP에서 #N/A가 표시된다는
것은 Excel이 기준표의 첫 번째 열에서 정확히 같은 값을 찾지 못했다는
뜻입니다.
먼저 기준표의 상품코드 열인 F열에서 A2 값을 직접 검색합니다.
또는
Ctrl+F를 누른 뒤 A2의 값을 검색합니다.
값이 없다면 수식 오류가 아니라 기준표에 해당 코드가 없는 상태입니다. 원본에 값을 추가하거나 “등록되지 않은 코드”로 처리해야 합니다.
화면에서 비슷한 값이 보이더라도 다음 값은 서로 다른 값입니다.
AB-100
AB100
AB-0100
AB-100 마지막 예제에는 표시하기 어려운 뒤쪽 공백이 포함됐을 수 있습니다.
2. 정확히 일치하려면 FALSE를 사용합니다
다음 수식은 네 번째 인수가 생략돼 있습니다.
=VLOOKUP(
A2,
$F$2:$H$1000,
3
)네 번째 인수를 생략하면 VLOOKUP은 기본적으로 근사 일치를 사용합니다. 첫 번째 열이 적절하게 정렬되지 않았다면 잘못된 값을 반환하거나 예상하지 못한 결과가 나올 수 있습니다.
상품코드·주문번호·사번처럼 동일한 값을 찾아야 한다면 다음처럼 작성합니다.
=VLOOKUP(
A2,
$F$2:$H$1000,
3,
FALSE
)
0도 정확히 일치를 의미하지만, 수식의 목적을 읽기 쉽게 하려면
FALSE를 사용할 수 있습니다.
3. 숫자와 문자 형식이 다른지 확인합니다
화면에 모두 1001이라고 표시되더라도 한 셀은 숫자이고 다른 셀은 문자로 저장돼 있을 수 있습니다.
A2가 숫자인지 확인합니다.
=ISNUMBER(A2)A2가 문자인지 확인합니다.
=ISTEXT(A2)기준표의 F2에도 같은 검사를 적용합니다.
=ISNUMBER(F2)
=ISTEXT(F2)조회 값은 숫자인데 기준표는 문자이거나, 조회 값은 문자인데 기준표는 숫자라면 정확한 일치에서 같은 값으로 처리되지 않을 수 있습니다.
계산에 사용하는 숫자라면 숫자로 통일
수량·금액·순번처럼 실제 숫자 의미를 가진 값은 숫자로 통일합니다. 숫자로 저장된 텍스트를 변환하는 보조 열을 만들 수 있습니다.
=VALUE(A2)
변환할 수 없는 문자나 기호가 포함돼 있으면 VALUE도 오류를
반환하므로 원본 값을 먼저 확인합니다.
여러 셀이 숫자로 저장된 텍스트라면 범위를 선택한 뒤 표시되는 오류 아이콘의 숫자로 변환을 사용하거나, 원본 구조를 확인한 뒤 데이터 → 텍스트 나누기 → 완료를 사용할 수 있습니다.
상품코드나 사번은 텍스트로 유지할 수 있음
다음 값처럼 앞의 0이 의미를 가진다면 숫자로 바꾸면 안 됩니다.
00125
01234
00007상품코드·사번·우편번호처럼 계산하지 않는 식별자는 조회 값과 기준표를 모두 텍스트 형식으로 통일해야 합니다.
앞의 0이 식별 정보라면 숫자로 변환하는 순간 값이 달라집니다.
계산값인지 식별코드인지 먼저 구분한 뒤 데이터 유형을 결정합니다.
4. 앞뒤 공백과 숨은 문자를 정리합니다
다른 시스템, 웹페이지, PDF 또는 CSV에서 복사한 값에는 화면에 보이지 않는 공백과 제어 문자가 포함될 수 있습니다.
A2의 전체 문자 수를 확인합니다.
=LEN(A2)일반 공백을 정리한 뒤의 문자 수와 비교합니다.
=LEN(TRIM(A2))두 결과가 다르면 시작·끝 또는 연속된 일반 공백이 포함됐을 가능성이 있습니다.
일반 공백과 일부 인쇄되지 않는 문자를 정리합니다.
=TRIM(
CLEAN(A2)
)다만 웹페이지에서 복사된 값에는 일반 공백이 아닌 줄 바꿈 없는 공백 문자 160이 포함될 수 있습니다. 이 문자는 TRIM만으로 제거되지 않을 수 있습니다.
문자 160을 일반 공백으로 바꾼 뒤 정리하는 수식입니다.
=TRIM(
CLEAN(
SUBSTITUTE(
A2,
CHAR(160),
" "
)
)
)이 수식은 일반적인 데이터 정리에 사용할 수 있지만 모든 종류의 유니코드 숨은 문자를 제거한다고 보장할 수는 없습니다.
사람 이름이나 주소처럼 단어 사이 공백이 의미를 가진 데이터에서는 공백을 모두 삭제하지 않습니다. 상품코드에서 공백 사용이 금지된다는 업무 규칙이 있을 때만 모든 공백 제거를 검토합니다.
조회 값과 기준표를 같은 방식으로 정리합니다
조회 값만 정리하고 기준표의 값은 그대로 두면 두 값이 계속 일치하지 않을 수 있습니다.
기준표의 F열 왼쪽에 보조 열 E를 추가했다고 가정합니다. E2에 다음 수식을 입력하고 필요한 행까지 채웁니다.
=TRIM(
CLEAN(
SUBSTITUTE(
F2,
CHAR(160),
" "
)
)
)조회 값 A2도 같은 방식으로 정리해 보조 셀 B2에 입력합니다.
=TRIM(
CLEAN(
SUBSTITUTE(
A2,
CHAR(160),
" "
)
)
)정리된 E열을 범위의 첫 번째 열로 사용해 H열 단가를 반환합니다. E:H 범위에서 H열은 네 번째 열입니다.
=VLOOKUP(
B2,
$E$2:$H$1000,
4,
FALSE
)원본을 바로 덮어쓰기보다 보조 열에서 정리 결과를 먼저 확인하면 어떤 값이 변경되는지 구분하기 쉽습니다.
5. 조회 값은 지정 범위의 첫 번째 열에 있어야 합니다
VLOOKUP은 지정한 범위의 첫 번째 열에서 조회 값을 검색합니다.
상품코드가 G열에 있는데 범위를 F:H로 지정하면 VLOOKUP은 F열에서 상품코드를 찾습니다.
상품코드가 G열이고 반환할 단가가 H열이라면 범위를 G:H로 시작합니다.
=VLOOKUP(
A2,
$G$2:$H$1000,
2,
FALSE
)지정한 범위에서 G열이 첫 번째 열이고 H열이 두 번째 열이므로 반환 열 번호는 2입니다.
조회 열보다 왼쪽 값을 반환해야 할 때
VLOOKUP은 조회 열의 오른쪽에 있는 값만 반환할 수 있습니다.
예를 들어 G열의 상품코드를 조회해 왼쪽 F열의 분류를 반환해야 한다면 일반적인 VLOOKUP 범위로는 처리하기 어렵습니다.
XLOOKUP을 지원하는 Excel에서는 조회 범위와 반환 범위를 별도로 지정할 수 있습니다.
=XLOOKUP(
A2,
$G$2:$G$1000,
$F$2:$F$1000,
"찾는 값 없음"
)XLOOKUP은 기본적으로 정확히 일치하는 항목을 찾고, 네 번째 인수에는 일치 항목이 없을 때 표시할 값을 지정할 수 있습니다.
XLOOKUP은 Excel 2016과 Excel 2019에서는 사용할 수 없으므로 파일을 사용하는 사람들의 Excel 버전을 확인해야 합니다.
6. 수식을 복사할 때 기준 범위가 이동하지 않게 합니다
다음 수식은 기준표 범위가 상대 참조입니다.
=VLOOKUP(
A2,
F2:H1000,
3,
FALSE
)
이 수식을 아래로 복사하면 다음 행에서는 범위가
F3:H1001로 이동합니다. 기준표 첫 행이 조회 범위에서 빠지고
필요하지 않은 아래 행이 추가될 수 있습니다.
기준표가 고정돼 있다면 절대 참조를 사용합니다.
=VLOOKUP(
A2,
$F$2:$H$1000,
3,
FALSE
)조회 값 A2는 아래로 복사할 때 A3, A4로 바뀌어야 하므로 상대 참조로 유지하고, 기준표만 절대 참조로 고정합니다.
7. 별표와 물음표가 포함된 코드를 확인합니다
정확히 일치하는 VLOOKUP에서도 별표와 물음표는 와일드카드로 해석될 수 있습니다.
*: 여러 문자?: 문자 한 개~: 다음 와일드카드를 일반 문자로 처리
실제 상품코드가 A*01이고 별표 자체를 찾으려면 별표 앞에
물결표를 사용합니다.
=VLOOKUP(
"A~*01",
$F$2:$H$1000,
3,
FALSE
)상품코드에 별표나 물음표가 실제 문자로 사용된다면 수식이 와일드카드로 해석하지 않도록 처리해야 합니다.
8. 근사 일치는 구간표에서만 목적에 맞게 사용합니다
TRUE가 항상 잘못된 것은 아닙니다. 점수 구간, 세율 구간, 수량별
단가처럼 가장 가까운 하한값을 찾는 표에서는 근사 일치를 사용할 수 있습니다.
예를 들어 J2:K6에 다음 기준표가 있다고 가정합니다.
| 최저 점수 | 등급 |
|---|---|
| 0 | F |
| 60 | D |
| 70 | C |
| 80 | B |
| 90 | A |
E2의 점수에 해당하는 등급을 찾는 수식입니다.
=VLOOKUP(
E2,
$J$2:$K$6,
2,
TRUE
)근사 일치를 사용할 때 기준표의 첫 번째 열은 오름차순으로 정렬돼 있어야 합니다. 정렬되지 않으면 잘못된 등급이 반환될 수 있습니다.
조회 값이 기준표의 가장 작은 값보다 작으면 근사 일치에서도
#N/A가 발생할 수 있습니다.
상품코드·사번·주문번호는 정확히 일치하는 FALSE가 적절합니다.
점수·세율·수량 구간은 정렬된 기준표에서 TRUE를 사용할 수 있습니다.
IFERROR로 모든 오류를 먼저 숨기지 않습니다
다음 수식은 VLOOKUP에서 발생하는 모든 오류를 빈칸으로 바꿉니다.
=IFERROR(
VLOOKUP(
A2,
$F$2:$H$1000,
3,
FALSE
),
""
)
실제 값이 없는 #N/A뿐 아니라 잘못된 열 번호, 잘못된 참조와 다른
계산 오류까지 빈칸으로 표시됩니다.
오류를 진단하는 동안에는 IFERROR를 제거해 원래 오류 문구를 확인합니다.
#N/A만 안내 문구로 바꾸려면 IFNA를 사용합니다
조회 값이 없을 때만 사용자용 안내를 표시하고 다른 오류는 그대로 확인하려면
IFNA를 사용할 수 있습니다.
=IFNA(
VLOOKUP(
A2,
$F$2:$H$1000,
3,
FALSE
),
"등록되지 않은 상품코드"
)
IFNA는 수식 결과가 #N/A일 때만 지정한 값을 반환합니다. 열
번호가 잘못돼 발생한 #REF! 같은 다른 오류는 숨기지 않습니다.
다만 IFNA도 원인을 수정하는 함수는 아닙니다. 기준표에 값이 정말 없는지, 숫자·문자 형식이나 공백 문제인지 먼저 확인한 뒤 사용합니다.
#N/A와 다른 VLOOKUP 오류를 구분합니다
| 오류 | 주요 의미 | 첫 확인 위치 |
|---|---|---|
#N/A
|
일치하는 값을 찾지 못함 | 실제 값, FALSE, 형식, 공백, 첫 번째 열 |
#REF!
|
반환 열 번호가 지정 범위를 벗어남 | 범위의 열 수와 세 번째 인수 |
#VALUE!
|
함수 인수나 열 번호가 유효하지 않음 | 세 번째 인수와 수식 구조 |
#NAME?
|
함수명이나 문자열 따옴표 문제 | VLOOKUP 철자와 직접 입력한 문자열 |
#SPILL!
|
여러 조회 값을 사용한 결과가 펼쳐지지 않음 | 조회값 전체 열 참조와 결과 출력 영역 |
같은 코드가 여러 개면 첫 번째 결과가 반환됩니다
기준표에 동일한 상품코드가 여러 행에 있어도 VLOOKUP은 오류를 표시하지 않고 위에서 처음 찾은 값을 반환합니다.
조회 코드가 기준표에 몇 번 있는지 확인할 수 있습니다.
=COUNTIF(
$F$2:$F$1000,
A2
)결과가 0이면 일치 항목이 없고, 1이면 하나이며, 2 이상이면 중복값이 있습니다.
업무상 상품코드가 고유해야 한다면 중복 행 중 어느 값을 반환할지 수식으로 임의 결정하기보다 원본 기준표를 정리하는 편이 적절합니다.
XLOOKUP으로 변경할 때도 데이터 정리는 필요합니다
XLOOKUP은 기본값이 정확히 일치이고 조회 열의 왼쪽이나 오른쪽 값을 반환할 수 있어 VLOOKUP의 범위 구조를 단순화할 수 있습니다.
=XLOOKUP(
A2,
$F$2:$F$1000,
$H$2:$H$1000,
"등록되지 않은 상품코드"
)그러나 조회 값과 기준 열의 숫자·문자 형식이 다르거나 숨은 문자가 포함돼 있다면 XLOOKUP에서도 값을 찾지 못할 수 있습니다.
함수를 바꾸기 전에 원본 데이터가 같은 유형과 정리 규칙을 사용하는지 확인해야 합니다.
수식이 맞는데 결과가 이전 값으로 남아 있을 때
#N/A가 아니라 원본을 변경했는데도 이전 결과가 계속 표시된다면
VLOOKUP의 일치 문제가 아닐 수 있습니다.
다음 항목을 확인합니다.
- Excel 계산 옵션이 수동으로 설정됐는지
- 조회 범위가 새로 추가한 행까지 포함하는지
- 기준표가 다른 통합 문서에 있고 최신 값으로 저장됐는지
- 수식 대신 과거 계산 결과 값이 붙여 넣어졌는지
계산 옵션과 수식 표시 문제는 아래 관련 글에서 별도로 확인할 수 있습니다.
VLOOKUP #N/A 최종 점검 순서
값이 실제로 존재하는지 먼저 확인합니다.
상품코드와 사번은 근사 일치가 아니라 정확히 일치를 사용합니다.
ISNUMBER와 ISTEXT로 조회 값과 기준 열의 데이터 유형을 확인합니다.
TRIM, CLEAN과 SUBSTITUTE를 보조 열에서 같은 방식으로 적용합니다.
VLOOKUP은 지정한 범위의 가장 왼쪽 열에서 조회 값을 찾습니다.
수식을 아래로 복사할 때 기준표가 이동하지 않도록 $ 기호를 사용합니다.
실제 코드에 별표나 물음표가 있다면 물결표로 일반 문자임을 표시합니다.
COUNTIF 결과가 2 이상이면 어떤 행을 기준으로 사용할지 원본에서 정합니다.
실제로 없는 값만 사용자용 안내 문구로 바꾸고 다른 오류는 숨기지 않습니다.
내용 확인에 사용한 Microsoft 공식 자료
공식 문서 확인일: 2026년 7월 29일
댓글 쓰기