엑셀 VLOOKUP 안될 때: #N/A, 엉뚱한 값, #REF!

1네 번째 인수를 빠뜨렸어요(유사 일치)

range_lookup을 생략하면 VLOOKUP은 TRUE, 즉 유사 일치로 찾아요. 첫 열이 정렬돼 있다고 가정하고, 정확한 값이 없으면 그보다 작은 값 중 가장 가까운 값을 돌려줘요. 정렬이 안 된 데이터에서는 엉뚱한 행의 값을 주거나 있는 값도 #N/A를 낼 수 있어요.

이렇게 확인해요

=VLOOKUP(F2,A2:C100,3)처럼 인수가 세 개뿐이고, 목록에 없는 코드인데도 값이 나오거나 바로 윗행의 값이 나와요.

해결 방법

=VLOOKUP(F2,A2:C100,3,FALSE)

FALSE 대신 0을 써도 같아요. 유사 일치는 세율 구간이나 학점 기준표처럼 첫 열을 일부러 정렬해 둔 경우에만 쓰세요.

2표 범위를 $로 고정하지 않았어요

이렇게 확인해요

위쪽 몇 행은 잘 되는데 아래로 갈수록 있는 값도 #N/A가 나와요. 아래쪽 수식을 클릭해 보면 범위가 A12:C111처럼 밀려 내려가 있어요.

해결 방법

표 범위를 $ 기호로 고정하세요. 수식에서 범위를 선택하고 F4를 누르면 돼요.

=VLOOKUP(F2,$A$2:$C$100,3,FALSE)

예방 팁. 목록을 표(삽입 > 표)로 만들고 =VLOOKUP(F2,제품,3,FALSE)처럼 표 이름으로 참조하세요. 표 참조는 밀리지 않고 새 행이 생기면 같이 늘어나요.

3열 번호(col_index_num)가 틀렸어요(#REF! 또는 다른 열 값)

열 번호는 시트의 A열이 아니라 표 범위의 첫 열부터 세요.

이렇게 확인해요

• #REF!: 열 번호가 범위의 열 수보다 커요. =VLOOKUP(F2,A2:C100,4,FALSE)는 3열짜리 범위에서 4번째 열을 달라는 거예요.
• #VALUE!: 열 번호가 1보다 작아요.
• 다른 열의 값이 나와요: 범위는 B열부터인데 A열부터 셌어요.

해결 방법

범위의 왼쪽 끝부터 다시 세거나, 원하는 열까지 범위를 넓히세요.

4표 중간에 열을 넣거나 지웠어요

숫자로 적은 열 번호는 열이 움직여도 바뀌지 않아요. 표 범위 안에 열을 하나 넣으면 3이 다른 열을 가리키게 되고, 오류 없이 엉뚱한 값이 나와요.

해결 방법

MATCH로 머리글을 찾아 열 번호를 정하게 하세요.

=VLOOKUP(F2,$A$2:$D$100,MATCH("가격",$A$1:$D$1,0),FALSE)

또는 결과 열을 직접 가리키는 XLOOKUP으로 바꾸세요(Excel 2021, Microsoft 365).

=XLOOKUP(F2,$A$2:$A$100,$C$2:$C$100)

5가져올 값이 찾는 열보다 왼쪽에 있어요

VLOOKUP은 범위의 첫 열에서만 찾고, 그 오른쪽 열만 돌려줘요. C열의 이메일로 찾아서 A열의 이름을 가져올 수는 없어요.

해결 방법

XLOOKUP을 쓰세요.

=XLOOKUP(F2,$C$2:$C$100,$A$2:$A$100)

모든 버전에서 되는 INDEX와 MATCH도 있어요.

=INDEX($A$2:$A$100,MATCH(F2,$C$2:$C$100,0))

6값이 실제로는 달라요(공백, 텍스트 숫자)

FALSE도 넣고 범위도 맞는데 #N/A가 남아 있다면, 대개 눈에 안 보이는 차이 때문이에요. 공백이 붙었거나 한쪽은 텍스트 숫자, 다른 쪽은 진짜 숫자인 경우예요.

해결 방법

=VLOOKUP(TRIM(F2),$A$2:$C$100,3,FALSE)

코드가 텍스트로 저장돼 있다면 찾을 값에 F2&""를, 숫자라면 --F2를 쓰세요. 자세한 내용은 XLOOKUP #N/A 해결 가이드에 있고, VLOOKUP에도 똑같이 적용돼요.

정말 없는 값일 때 #N/A 대신 메시지를 보여 주려면 이렇게 쓰세요.

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