엑셀 vlookup 함수 오류 원인별로 고치는 방법

며칠 전 부품 재고표에서 품번으로 가격을 불러오는데, 분명 표에 있는 품번이 #N/A로 떴습니다. 이런 경우 수식을 통째로 다시 쓰기보다 오류가 난 셀 하나를 골라 조회값, 표 범위, 열 번호를 차례로 확인하는 편이 빠릅니다. 엑셀 vlookup 함수 오류는 화면에 표시된 오류 종류에 따라 확인할 곳도 달라집니다.

먼저 수식의 네 자리를 확인합니다

예를 들어 A열이 품번, B열이 제품명, C열이 가격이고 E2의 품번으로 가격을 찾는다면 수식은 =VLOOKUP(E2,$A$2:$C$500,3,FALSE)입니다. E2는 찾을 값, A2:C500은 검색할 표, 3은 그 표 안에서 가격이 있는 세 번째 열, FALSE는 정확히 일치하는 값을 찾으라는 뜻입니다.

가장 먼저 볼 것은 검색할 품번이 표 범위의 맨 왼쪽 열에 있는지입니다. 위 수식은 A열에서 품번을 찾고 C열의 값을 돌려줍니다. 품번이 B열에 있는데 범위를 A열부터 잡았다면 VLOOKUP은 B열을 검색하지 않습니다. 범위의 시작 열을 바꾸거나 표 배치를 조정해야 합니다.

수식을 아래로 복사할 때 표 범위가 함께 밀리는 경우도 흔합니다. 첫 행에서는 A2:C500을 보다가 다음 행에서는 A3:C501을 보게 되는 식입니다. 조회 셀 E2는 E3, E4로 바뀌어야 하지만 표 범위는 고정돼야 하므로 $A$2:$C$500처럼 달러 표시를 붙입니다.

#N/A가 뜨면 품번이 정말 같은지 봅니다

#N/A는 정확히 일치하는 값을 찾지 못했다는 뜻입니다. 눈으로 같은 품번처럼 보여도 한쪽은 숫자 12345, 다른 쪽은 문자로 저장된 12345일 수 있습니다. 빈 셀에서 =ISNUMBER(E2)와 =ISTEXT(E2)를 확인하고, 표의 품번 셀에도 같은 검사를 해보면 구분하기 쉽습니다. 숫자로 쓸 품번이라면 데이터 형식을 통일하고, 00123처럼 앞자리 0이 의미 있는 품번이라면 양쪽을 문자로 유지하는 편이 안전합니다.

공백도 자주 걸립니다. 거래처에서 받은 목록에는 품번 앞뒤의 일반 공백이나 웹에서 복사할 때 들어온 특수 공백이 섞이기도 합니다. 의심되는 값을 옆 셀에 =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))로 처리해 원본과 비교합니다. 조회값 쪽에도 같은 문제가 있을 수 있으니 E2도 확인해야 합니다. 보조 열로 값을 손본 뒤에는 그 열을 기준으로 검색하거나, 검증을 마친 값을 원래 열에 붙여 넣습니다.

정확히 일치하는 품번이 표에 없다면 수식으로 해결할 문제는 아닙니다. 검색 범위에 새 행이 빠졌는지, 품번 자체가 잘못 입력됐는지부터 확인합니다. 오류를 숨기는 IFERROR는 이 확인이 끝난 뒤에 쓰는 게 좋습니다. 처음부터 빈칸으로 바꾸면 누락된 재고가 조용히 지나갑니다.

#REF!와 #VALUE!는 열 번호를 확인합니다

위 예시의 A:C는 세 열짜리 범위입니다. 그런데 열 번호를 4로 적으면 범위 안에 네 번째 열이 없으므로 #REF!가 납니다. 열을 삭제하거나 표 범위만 줄인 뒤 예전 열 번호를 그대로 둔 경우에도 생깁니다. 열 번호는 시트 전체의 열 번호가 아니라 지정한 범위의 왼쪽부터 세는 번호입니다. B:D를 범위로 잡았다면 D열은 3입니다.

열 번호에 0이나 음수를 넣으면 #VALUE!가 납니다. 수식의 열 번호가 다른 셀을 참조한다면 그 셀에 문자나 잘못된 계산 결과가 들어갔는지도 봅니다. 에러 셀을 클릭해 수식 표시줄에서 범위와 열 번호를 함께 읽으면 어긋난 부분이 금방 보입니다.

값은 나오는데 틀리다면 일치 방식과 중복을 봅니다

오류 표시 없이 엉뚱한 가격이 나오는 쪽이 더 곤란합니다. 네 번째 인수를 빼거나 TRUE로 쓰면 근사값 검색이 적용됩니다. 이 방식은 검색 열이 오름차순으로 정렬돼 있어야 하며, 품번처럼 정확히 같은 항목을 찾을 때는 맞지 않습니다. 품번 조회 수식 끝에는 FALSE 또는 0을 명시합니다.

FALSE를 썼는데도 예상과 다른 값이라면 같은 품번이 여러 번 들어 있는지 확인합니다. VLOOKUP은 위에서 처음 만난 일치 항목의 값을 가져옵니다. 오래된 가격 행이 위에 남아 있으면 새 가격이 아래에 있어도 옛 값이 표시됩니다. 검색 열에 필터를 걸어 해당 품번의 중복 행을 확인하고, 어떤 행을 기준으로 쓸지 정해야 합니다.

실무에서는 #N/A를 보자마자 수식을 늘리기보다 오류가 난 품번 하나로 원본 데이터를 확인하는 쪽이 덜 돌아갑니다. 값의 형식과 공백을 맞추고, 범위와 열 번호를 확인한 다음, 정확히 일치하는 검색인지 보면 대부분의 원인이 드러납니다.