엑셀 VLOOKUP이 안 될 때 확인할 것들

안녕하세요? 일랑입니다.

 

분명히 값을 입력했는데 VLOOKUP만 쓰면 #N/A가 뜨는 경험, 직장인이라면 한 번쯤 있으실 겁니다.

저도 이런 상황에서 수식을 지웠다 썼다 반복하다 인터넷 검색으로 해결을 하곤 했는데요.

 

오늘은 VLOOKUP 함수 오류가 났을 때 확인해봐야 할 것들을 정리해볼게요!

---

오류 종류부터 구분하기

오류 메시지마다 그 원인이 다릅니다.

오류	의미	의심되는 원인
#N/A	찾는 값을 못 찾음	공백, 형식 불일치, 찾는 열 위치 문제 (이 글의 주제)
#REF!	열 번호가 범위를 벗어남	세 번째 인수(열 번호)가 선택한 범위의 열 개수보다 큼
#VALUE!	인수 값이 잘못됨	열 번호를 1보다 작게 입력한 경우 등

이 글에서는 가장 흔한 #N/A를 중심으로 설명해드리겠습니다.

 

기본 문법도 한 번 짚고 갈게요.

 

=VLOOKUP(찾을 값, 찾을 범위, 가져올 열 번호, 일치 방식)
=VLOOKUP(F2, $A$2:$C$100, 3, FALSE)

원인 1. 보이지 않는 공백

가장 흔한 원인입니다. 셀에 '홍길동'이라고 적혀 있어도, 실제로는 '홍길동 '처럼 뒤에 공백이 붙어 있으면 엑셀은 다른 값으로 인식해요. 다른 시스템에서 데이터를 복사해 올 때 특히 자주 생깁니다.

 

확인 방법

• 두 셀의 글자 수를 비교해보세요: =LEN(A2) 와 =LEN(F2)
• 눈으로 같아 보이는데 글자 수가 다르면 공백 문제입니다.

해결 방법

• 공백 제거 함수 사용: =TRIM(A2)
• 일반 TRIM으로 안 지워지는 공백(웹에서 복사한 데이터에 많음)은 =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) 로 처리할 수 있어요.
• 수식으로 변환한 값을 복사한 뒤 "값으로 붙여넣기"하면 원본 대신 사용할 수 있습니다.

원인 2. 숫자와 텍스트 형식 불일치

사원번호나 상품코드처럼 숫자로 된 값에서 자주 발생합니다.

한쪽은 숫자, 다른 한쪽은 텍스트로 저장된 숫자면 눈에는 똑같아도 엑셀은 서로 다른 값으로 봅니다. (예: 1001과 '1001)

 

확인 방법

• 셀 왼쪽 위에 초록색 삼각형이 보이면 텍스트로 저장된 숫자일 가능성이 높아요.
• =ISNUMBER(A2) 를 입력했을 때 FALSE가 나오면 숫자가 아니라 텍스트입니다.

해결 방법

• 초록색 삼각형이 있는 셀을 선택하고 나타나는 경고 아이콘에서 '숫자로 변환' 선택
• 또는 빈 셀에 1을 입력해 복사한 뒤, 문제가 되는 범위를 선택하고 선택하여 붙여넣기 → 곱하기를 하면 한 번에 숫자로 바뀝니다.
• 반대로 찾을 값을 텍스트로 맞춰야 할 때는 =A2&"" 처럼 변환할 수 있어요.

원인 3. 찾는 값이 범위의 첫 번째 열에 없음

VLOOKUP은 이름 그대로 범위의 가장 왼쪽 열에서만 값을 찾습니다. 찾을 값이 선택한 범위의 첫 번째 열에 있어야 하고, 가져올 값은 그 오른쪽에 있어야 해요.

 

예를 들어,

A열	B열	C열
이름	사원번호	부서

사원번호로 이름을 찾고 싶다면, 이름(A열)이 사원번호(B열)보다 왼쪽에 있어서 VLOOKUP으로는 불가능합니다.

해결 방법

• INDEX + MATCH 조합: =INDEX(A:A, MATCH(F2, B:B, 0)) 처럼 왼쪽 값도 가져올 수 있어요.
• XLOOKUP 사용: =XLOOKUP(F2, B:B, A:A) 로 훨씬 간단하게 해결됩니다. 다만 XLOOKUP은 Excel 2021과 Microsoft 365에서 쓸 수 있고, Excel 2019 이하 버전에서는 지원되지 않으니 회사 엑셀 버전을 먼저 확인하세요.

원인 4. 범위가 밀려버림 (절대참조 문제)

첫 번째 셀에서는 잘 되는데, 수식을 아래로 복사하면 오류가 나기 시작한다면 이 경우일 가능성이 높아요.

수식을 복사하면 범위도 같이 한 칸씩 내려가는데, 이러면 아래쪽 행에서는 찾을 범위에서 값이 사라져버립니다.

첫 번째 행:  =VLOOKUP(F2, A2:C100, 3, FALSE)
두 번째 행:  =VLOOKUP(F3, A3:C101, 3, FALSE)   ← 범위가 밀림

해결 방법

• 찾을 범위를 선택한 상태에서 F4 키를 눌러 $A$2:$C$100 처럼 고정(절대참조)하세요.
• 노트북에서는 Fn + F4 로 입력해야 하는 경우가 있습니다.

원인 5. 마지막 인수를 잘못 입력함

VLOOKUP의 네 번째 인수는 **정확히 일치할 값을 찾을지(FALSE 또는 0), 비슷한 값을 찾을지(TRUE 또는 1)**를 정합니다.

• FALSE (또는 0): 정확히 일치하는 값만 찾음 → 대부분의 경우 이걸 쓰면 됩니다
• TRUE (또는 1) 또는 생략: 근사값을 찾음 → 데이터가 오름차순 정렬되어 있어야 제대로 동작

이 인수를 생략하면 근사 일치로 동작해서, 엉뚱한 값이 나오거나 오류가 나는 일이 생깁니다. 정확한 값을 찾는 상황이라면 마지막에 꼭 FALSE를 넣으세요.

오류 해결 후: IFERROR 쓸 때 주의할 점

오류를 해결한 뒤 #N/A를 보기 싫은 대체값으로 바꾸고 싶다면 IFERROR를 쓸 수 있습니다.

=IFERROR(VLOOKUP(F2, $A$2:$C$100, 3, FALSE), "해당 없음")

 

다만 원인을 파악하기 전에 IFERROR부터 씌우는 건 위험해요. 진짜로 데이터가 없는 건지, 공백이나 형식 문제로 못 찾는 건지 구분이 안 되기 때문입니다. 원인을 먼저 확인한 뒤에 마지막 단계로 사용하세요.

한눈에 보는 체크 순서

1. 두 셀의 LEN 값이 같은가? → 다르면 공백 제거 (TRIM)
2. ISNUMBER 결과가 서로 같은가? → 다르면 형식 통일
3. 찾는 값이 범위의 맨 왼쪽 열에 있는가?
4. 범위에 $ (절대참조)가 걸려 있는가?
5. 마지막 인수에 FALSE를 넣었는가?

이 다섯 가지만 순서대로 확인해도 VLOOKUP 오류의 대부분은 해결됩니다.

다음에 #N/A를 만나면 수식을 지우기 전에 이 체크리스트를 먼저 돌려보세요!