VLOOKUP이 안 될 때 확인할 6가지 - #N/A 오류부터 잘못된 값까지

VLOOKUP은 익히기는 쉬운데 실제 업무 파일에서는 생각보다 자주 어긋납니다. 어제까지 잘 되던 수식이 갑자기 #N/A를 표시하기도 하고, 수식을 아래로 복사했더니 일부 행에서만 값이 달라지는 경우도 있습니다.

문제는 VLOOKUP 자체가 어려워서라기보다 정확히 일치 옵션, 숫자와 텍스트 형식, 공백, 조회 범위, 열 위치 같은 작은 차이 때문인 경우가 많습니다.

저는 VLOOKUP을 확인할 때 아래 순서대로 봅니다. 특히 첫 번째와 두 번째 항목에서 원인이 발견되는 경우가 많습니다.

VLOOKUP 오류 원인 6가지 점검 순서

1. 네 번째 인수가 0인지 먼저 확인하기

VLOOKUP 오류를 확인할 때 가장 먼저 보는 부분입니다.

=VLOOKUP(A2,$B$2:$D$500,2,0)

마지막 0 또는 FALSE는 정확히 같은 값만 찾는다는 뜻입니다.

마지막 인수를 생략하면 근사 일치 방식으로 조회될 수 있기 때문에 원하는 값이 아닌 다른 값이 반환될 수 있습니다. 특히 코드, 거래처명, 관리번호처럼 정확히 같은 값을 찾아야 하는 작업에서는 마지막 인수를 명확하게 0으로 지정하는 편이 안전합니다.

예를 들어 아래 두 수식은 겉보기에는 비슷하지만 동작 방식이 다릅니다.

=VLOOKUP(A2,$B$2:$D$500,2)

=VLOOKUP(A2,$B$2:$D$500,2,0)

일반적인 코드 조회나 거래처 조회라면 두 번째처럼 마지막에 0을 넣어 정확히 일치하도록 설정합니다.

2. 찾는 값이 숫자인지 텍스트인지 확인하기

두 번째로 많이 발생하는 문제가 숫자와 텍스트 형식이 서로 다른 경우입니다.

예를 들어 한 파일에는 숫자 1001이 들어 있고 다른 파일에는 텍스트 형식의 "1001"이 들어 있을 수 있습니다. 셀 화면에서는 둘 다 1001로 보이지만 Excel 내부에서는 서로 다른 값으로 처리될 수 있습니다.

VLOOKUP에서 숫자와 텍스트 형식 불일치로 #N/A 오류가 발생하는 예제

간단하게 확인하려면 다음 함수를 사용할 수 있습니다.

=ISNUMBER(A2)

TRUE가 나오면 숫자이고, FALSE가 나오면 숫자가 아닌 형태일 가능성이 있습니다.

숫자로 바꿀 수 있는 텍스트라면 다음처럼 변환할 수도 있습니다.

=VALUE(A2)

또는 상황에 따라 A2*1처럼 숫자 연산을 이용해 변환하기도 합니다.

3. 앞뒤 공백이 숨어 있지 않은지 확인하기

거래처명이나 품목명처럼 문자 데이터를 조회할 때는 공백도 자주 문제가 됩니다.

예를 들어 화면에는 둘 다 대성물산으로 보이더라도 실제 데이터가 다음처럼 다를 수 있습니다.

  • 대성물산
  • 대성물산 

뒤쪽에 공백 하나가 들어 있으면 VLOOKUP에서는 서로 다른 값으로 판단할 수 있습니다.

찾는 값의 앞뒤 일반 공백을 정리할 때는 TRIM을 사용할 수 있습니다.

=VLOOKUP(TRIM(A2),$B$2:$D$500,2,0)

다만 이 수식은 A2의 공백만 정리합니다. 조회 대상 표의 값 자체에 공백이 들어 있다면 원본 데이터도 정리해야 합니다.

두 파일을 비교할 때 한쪽에만 공백이 섞여 있다면 보조 열을 하나 만들어 =TRIM(B2)처럼 정리한 값을 기준으로 조회하는 방법이 더 확실합니다.

4. 조회 범위를 절대참조로 고정했는지 확인하기

첫 행에서는 정상인데 수식을 아래로 복사할수록 #N/A가 늘어난다면 조회 범위를 확인합니다.

예를 들어 다음 수식은 조회 범위가 고정되어 있습니다.

=VLOOKUP(A2,$B$2:$D$500,2,0)

반대로 아래처럼 $가 없다면 수식을 아래로 복사할 때 범위도 같이 내려갑니다.

=VLOOKUP(A2,B2:D500,2,0)
VLOOKUP 조회 범위를 절대참조로 고정하지 않아 범위가 아래로 이동하는 예제

두 번째 행에서는 B3:D501, 그다음 행에서는 B4:D502처럼 기준 범위가 계속 이동하게 됩니다.

고정된 조회표라면 범위를 선택한 뒤 F4를 눌러 절대참조로 만드는 것이 편합니다.

데이터가 계속 늘어나는 목록이라면 조회 원본을 Ctrl+T로 표로 만들어 관리하는 것도 좋습니다. 표 이름과 열 이름을 이용하면 고정 셀 주소를 매번 수정할 필요가 줄어듭니다.

5. 찾는 값이 조회 범위의 첫 번째 열에 있는지 확인하기

VLOOKUP은 지정한 범위의 가장 왼쪽 열에서 찾을 값을 검색하고 그 오른쪽에 있는 값을 반환합니다.

A열 B열
코드 거래처명

코드를 기준으로 거래처명을 찾는 것은 쉽습니다. 하지만 거래처명을 기준으로 왼쪽에 있는 코드를 찾으려면 VLOOKUP 구조와 맞지 않습니다.

이런 경우 INDEX/MATCH를 사용할 수 있습니다.

=INDEX($B$2:$B$500,MATCH(A2,$D$2:$D$500,0))

MATCH가 찾는 값의 위치를 구하고, INDEX가 해당 위치의 결과를 가져오는 방식입니다.

Microsoft 365나 XLOOKUP을 지원하는 Excel 버전이라면 더 간단하게 처리할 수도 있습니다.

=XLOOKUP(A2,$D$2:$D$500,$B$2:$B$500,"없음")

6. 열을 추가한 뒤 잘못된 값이 나오지 않는지 확인하기

VLOOKUP의 세 번째 인수는 반환할 열 번호입니다.

예를 들어 다음 수식에서 3은 조회 범위의 세 번째 열을 가져오라는 뜻입니다.

=VLOOKUP(A2,$B$2:$E$500,3,0)

그런데 조회표 중간에 새 열을 추가하면 기존에 세 번째였던 값이 네 번째 열로 이동할 수 있습니다. 이 경우 수식 자체는 오류 없이 실행되면서 다른 열의 값을 가져올 수 있습니다.

VLOOKUP에서 열을 추가한 뒤 열 번호가 어긋나 잘못된 값을 반환하는 예제

그래서 표 구조가 자주 바뀌는 파일이라면 VLOOKUP보다 XLOOKUP이나 INDEX/MATCH가 관리하기 편한 경우가 많습니다.

7. 오류를 숨기기 전에 원인부터 확인하기

#N/A가 보고서에 그대로 표시되는 것이 불편해서 처음부터 IFERROR를 사용하는 경우가 있습니다.

=IFERROR(VLOOKUP(A2,$B$2:$D$500,2,0),"")

화면은 깔끔해지지만 문제 원인을 찾기 어려워질 수 있습니다. 실제 값이 없는 것인지, 숫자와 텍스트 형식이 다른 것인지, 범위가 틀린 것인지 모두 빈칸으로 보일 수 있기 때문입니다.

검증 단계에서는 차라리 다음처럼 표시하는 방법도 있습니다.

=IFERROR(VLOOKUP(A2,$B$2:$D$500,2,0),"확인 필요")

먼저 문제가 있는 행을 확인하고, 검증이 끝난 뒤 최종 보고서에서 빈칸 처리하는 편이 안전합니다.

8. 중복된 값이 여러 개 있는 경우

VLOOKUP은 조건에 맞는 값이 여러 개 있어도 기본적으로 처음 찾은 값을 반환합니다.

예를 들어 같은 거래처 코드가 원본에 세 번 들어 있다고 해서 세 건의 금액을 모두 더해주는 것은 아닙니다.

여러 행의 금액을 합산하는 것이 목적이라면 SUMIF 또는 SUMIFS처럼 집계용 함수를 사용하는 편이 맞습니다.

중복 자체가 잘못된 데이터라면 먼저 중복 여부를 확인한 뒤 원본을 정리해야 합니다.

9. 일부 문자만 포함돼 있어도 찾고 싶은 경우

정확히 같은 이름이 아니라 일부 문자열을 포함하는 항목을 찾고 싶다면 와일드카드 *를 사용할 수 있습니다.

=VLOOKUP("*"&A2&"*",$B$2:$D$500,2,0)

다만 같은 문자를 포함하는 값이 여러 개라면 처음 일치하는 결과가 반환될 수 있으므로 결과를 반드시 확인하는 것이 좋습니다.

VLOOKUP 오류 확인 순서

  1. 마지막 인수가 0인지 확인합니다.
  2. 찾는 값과 원본 값의 숫자·텍스트 형식을 비교합니다.
  3. 앞뒤 공백이 들어 있지 않은지 확인합니다.
  4. 조회 범위가 절대참조로 고정됐는지 확인합니다.
  5. 찾는 값이 조회 범위의 첫 번째 열에 있는지 확인합니다.
  6. 열을 추가하거나 삭제하면서 열 번호가 바뀌지 않았는지 확인합니다.

이 순서대로 확인하면 무작정 수식을 다시 만드는 것보다 문제 지점을 빠르게 좁힐 수 있습니다.

정리

  • 정확히 같은 값을 조회하려면 마지막 인수를 0으로 지정합니다.
  • 숫자와 텍스트는 화면에서 같아 보여도 다른 값일 수 있습니다.
  • 문자 데이터는 앞뒤 공백도 함께 확인합니다.
  • 수식을 아래로 복사한다면 조회 범위를 절대참조로 고정합니다.
  • VLOOKUP은 조회 범위의 가장 왼쪽 열에서 값을 찾습니다.
  • 열 구조가 자주 바뀌는 표라면 XLOOKUP이나 INDEX/MATCH도 검토합니다.
  • IFERROR는 오류 원인을 확인한 뒤 사용하는 편이 좋습니다.

VLOOKUP에서 가장 위험한 경우는 오류가 표시되는 상황보다 수식은 정상인데 잘못된 값을 가져오는 상황입니다. 그래서 #N/A만 없애는 것보다 조회 조건과 원본 데이터가 정확한지 먼저 확인하는 과정이 중요합니다.

관련 글

댓글

댓글 쓰기