거래처 명단에서 담당자를 찾거나, 재고 코드로 품목명을 불러올 때 VLOOKUP만큼 자주 쓰는 함수도 없습니다. 그런데 분명 값이 있는데도 엑셀 VLOOKUP 안될 때가 있습니다. #N/A만 뜨거나, 엉뚱한 값이 나오거나, 아무 이유 없이 멈춰버리는 경우입니다. 이런 문제는 대부분 함수 자체의 오류가 아니라 데이터 형식이나 참조 범위 설정에서 발생합니다.
이 글에서는 VLOOKUP이 작동하지 않는 대표적인 원인 5가지를 실무 예시로 짚어보고, 각각의 해결법을 정리합니다. 마지막에는 VLOOKUP의 한계를 넘어서는 대안 함수도 소개합니다.
엑셀 VLOOKUP 안될 때 가장 먼저 확인할 것은 무엇인가요
VLOOKUP이 안 되는 경우 90% 이상은 찾는 값과 참조표의 데이터 형식이 다르거나, 참조 범위에 절대참조($)가 빠져 있기 때문입니다. 텍스트로 저장된 숫자와 실제 숫자가 섞여 있으면 겉보기엔 같아도 엑셀은 다른 값으로 인식합니다. 먼저 이 두 가지부터 점검하면 절반 이상의 문제가 해결됩니다.
원인 1. 숫자처럼 보이지만 텍스트로 저장된 값
거래처 코드나 사번을 다른 시스템에서 복사해 붙였을 때 자주 생기는 문제입니다. 셀 왼쪽에 초록색 삼각형이 있거나, 셀이 왼쪽 정렬돼 있다면 텍스트로 저장된 숫자입니다.
=VLOOKUP(A2, 참조표!A:B, 2, FALSE)이 수식에서 A2가 텍스트 “1001”이고 참조표의 A열은 숫자 1001이라면 일치하지 않아 #N/A가 뜹니다. 해결법은 두 가지입니다.
=VLOOKUP(TEXT(A2,"0"), 참조표!A:B, 2, FALSE)
=VLOOKUP(VALUE(A2), 참조표!A:B, 2, FALSE)찾는 값이 텍스트인지 숫자인지에 따라 반대로 변환해 형식을 맞춰주면 됩니다. 데이터를 가져올 때부터 형식을 통일하는 방법도 있는데, 이 부분은 비전공자를 위한 데이터 클렌징 입문에서 더 자세히 다뤘습니다.
원인 2. 참조 범위에 셀 주소가 고정되지 않음
수식을 아래로 복사하면 참조 범위(테이블 배열)도 함께 밀려 내려가면서 엉뚱한 범위를 참조하게 됩니다.
=VLOOKUP(A2, 참조표!$A$2:$B$100, 2, FALSE)참조표 부분에 F4 키를 눌러 절대참조를 걸어두면 수식을 아래로 복사해도 참조 범위가 고정됩니다. 이 하나만 지켜도 “분명 처음엔 됐는데 아래 셀은 안 된다”는 문제가 대부분 사라집니다.
원인 3. 찾는 값 앞뒤에 보이지 않는 공백
다른 시스템에서 내려받은 데이터나 공문에서 복사한 텍스트에는 눈에 보이지 않는 공백이나 줄바꿈이 섞여 있는 경우가 많습니다. 이때는 TRIM 함수로 감싸서 해결합니다.
=VLOOKUP(TRIM(A2), 참조표!A:B, 2, FALSE)여전히 안 된다면 CLEAN 함수를 추가로 씌워 줄바꿈 문자까지 제거해봅니다.
=VLOOKUP(TRIM(CLEAN(A2)), 참조표!A:B, 2, FALSE)원인 4. 마지막 인수를 생략하거나 TRUE로 둠
VLOOKUP의 네 번째 인수는 정확히 일치하는 값을 찾을지(FALSE), 근사값을 찾을지(TRUE)를 결정합니다. 이 인수를 생략하면 기본값이 TRUE로 처리되어, 정렬되지 않은 데이터에서는 전혀 다른 값이 반환됩니다.
=VLOOKUP(A2, 참조표!A:B, 2)위처럼 네 번째 인수 없이 쓰면 근사 일치로 작동합니다. 정확한 값을 찾을 때는 반드시 FALSE 또는 0을 명시해야 합니다.
원인 5. 찾는 열이 참조 범위의 첫 번째 열이 아님
VLOOKUP은 항상 참조 범위의 첫 번째 열에서만 값을 찾습니다. 담당자 이름으로 사번을 찾고 싶은데 참조표에서 사번이 이름보다 왼쪽에 있다면 VLOOKUP으로는 불가능합니다. 이 경우 열 순서를 바꾸거나, INDEX와 MATCH를 조합해야 합니다.
=INDEX(참조표!A:A, MATCH(A2, 참조표!B:B, 0))
VLOOKUP 대신 XLOOKUP을 써야 할 때
엑셀 최신 버전(마이크로소프트 365)을 쓰고 있다면 XLOOKUP이 위 문제들을 상당수 해결해줍니다. 왼쪽 열 제약이 없고, 값을 못 찾았을 때 표시할 텍스트도 바로 지정할 수 있습니다.
=XLOOKUP(A2, 참조표!B:B, 참조표!A:A, "일치 없음")다만 관공서나 일부 기업은 구버전 오피스를 계속 쓰는 경우가 많아, VLOOKUP과 INDEX-MATCH 조합은 여전히 기본으로 익혀둘 필요가 있습니다.
VLOOKUP 문제가 반복적으로 발생한다면 데이터 자체의 형식이 통일되지 않은 것이 근본 원인일 수 있습니다. 이럴 때는 매번 수식을 손보는 대신 데이터를 불러오는 단계에서 정리하는 방법도 고려할 만한데, 엑셀 반복 업무를 파이썬으로 자동화하면 형식 변환까지 한 번에 처리할 수 있습니다. 또한 참조표를 여러 시트에서 관리하고 있다면 피벗 테이블 새로고침 방법과 함께 원본 데이터 구조를 점검해보는 것도 도움이 됩니다.
엑셀 함수만으로 반복되는 데이터 정리와 참조 작업이 계속 번거롭다면, 업무 특성에 맞는 자동화나 데이터 가공 방식을 함께 검토해볼 필요가 있습니다. 픽셀앤코드는 이런 실무 환경에서 반복되는 데이터 작업을 진단하고, 엑셀 수준을 넘어서는 자동화 도구나 맞춤형 프로그램으로 연결하는 작업을 함께 진행하고 있습니다.