엑셀에서 VLOOKUP 함수가 올바르지 않은 값 반환할 때

이 글은 VLOOKUP 함수가 예상과 다른 결과를 반환하거나 오류를 표시할 때의 원인과 해결책을 정리하여 실무자들이 신속하게 문제를 파악하고 수정할 수 있도록 돕기 위함이다.

VLOOKUP 함수의 기본 구조 이해

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) 형태로 동작하며, 세로 방향으로 검색 값을 찾은 뒤 지정 열의 데이터를 반환한다. [range_lookup] 인수가 TRUE이면 근사치 검색, FALSE이면 정확한 값 검색을 수행한다. 함수가 올바른 값을 반환하지 않는다면 먼저 이 네 가지 인수의 설정을 검토해야 한다.

잘못된 값이 반환되는 주요 원인

• 정렬 문제: 근사치 모드에서 lookup_value 열이 오름차순으로 정렬되지 않음
• 데이터 형식 불일치: 숫자·텍스트 혼합, 숨은 공백·특수문자
• 열 번호 착오: col_index_num이 검색 범위를 벗어남
• 범위 고정 누락: 셀 복사 시 $ 고정 실수
• 병합 셀 사용: 조회 열 또는 반환 열에 병합 셀이 존재
• 숨은 문자·제어 문자 섞임: 웹 복사 데이터, CSV 가져오기 등

원인별 해결 방법

유형	증상	점검 사항	해결 방안
정렬 오류	근사치 모드에서 엉뚱한 행 반환	키 열 오름차순 정렬 여부	정확히 정렬하거나 FALSE로 변경하다.
데이터 형식 불일치	#N/A 또는 빈 셀 반환	숫자·텍스트 구분, TRIM 필요 여부	VALUE(), TEXT(), TRIM() 사용해 통일하다.
열 번호 착오	다른 열의 값 표시	col_index_num 재확인	정확한 열 번호 입력하다.
범위 고정 누락	복사 후 참조 범위 밀림	절대참조 여부	$로 절대참조 고정하다.
병합 셀	예상 행·열 어긋남	병합 여부	병합 해제 후 정상화하다.
숨은·제어 문자	#N/A, 불일치	CHAR(160), CHAR(10) 검출	CLEAN(), SUBSTITUTE()로 제거하다.

실습 예제: 오류 재현 및 수정

1단계: 오류 상황 만들기

제품코드 | 제품명
-----------------
A001     | 사과
A002     | 배
A003     | 바나나

위 표가 B2:C4 범위라고 가정하고, 다른 시트에서 A003을 조회한다.

=VLOOKUP("A003", Sheet1!$B$2:$C$4, 2, FALSE)

그러나 제품명이 반환되지 않고 #N/A 오류가 발생한다.

2단계: 원인 진단

1. 찾는 값 "A003"에 공백이 포함되어 있을 수 있다.
2. 제품코드 열에 보이는 공백 외에 CHAR(160) 등의 특수 공백이 존재할 수 있다.

3단계: 해결

=VLOOKUP(TRIM("A003"), CLEAN(Sheet1!$B$2:$C$4), 2, FALSE)

또는 별도 도우미 열을 만들어 =TRIM(CLEAN(B2))처럼 정제한 값을 조회 열로 사용하면 더 효율적이다.

팁: 데이터가 많은 경우 Power Query를 이용해 불필요한 공백·제어 문자를 일괄 제거하면 속도와 유지 보수성이 향상된다.

VLOOKUP 함수 대신 사용 가능한 대안

• XLOOKUP: 동적 배열 지원, 왼쪽 검색 가능, 디폴트 정확도 FALSE이다.
• INDEX·MATCH 조합: 열 위치 변동에도 안정적이며, 다중 조건 검색에 유리하다.
• FILTER 함수: 조건이 여러 개인 경우 행 전체를 반환하여 데이터 분석 시 편리하다.

실무 팁 및 Best Practice

1. 테이블(Object) 활용: 범위가 늘어나도 자동 확장되어 col_index_num 오류를 줄인다.
2. 오류 처리 래핑: IFERROR(), IFNA()로 보고서의 가독성을 높인다.
3. 명명된 범위 사용: 수식 이해도를 높이고 유지보수성을 강화한다.
4. 데이터 검증(Data Validation) 설정: 잘못된 코드 입력을 사전에 차단한다.
5. 범용 도우미 열: 검색키를 생성·정제하는 열을 별도로 두면 다중 함수에서 재사용 가능하다.

FAQ

Q1. 근사치 모드에서 정확한 결과를 얻으려면 어떻게 정렬해야 하나?

A1. lookup_value가 위치한 첫 열을 오름차순(숫자·텍스트 동일 기준)으로 정렬한다. 혼합 형식이 있으면 텍스트가 숫자보다 뒤로 배치되므로 같은 형식으로 통일한 뒤 정렬하는 것이 안전하다.

Q2. 병합 셀이 많은 기존 양식을 유지한 채 VLOOKUP을 써야 할 때 대안은?

A2. 가급적 병합을 해제한 복제 시트를 만들어 함수 연산을 수행한 뒤, 결과값만 원본 시트로 연결하는 방식이 가장 안정적이다.

Q3. CSV를 불러오면 #N/A가 늘어나는데 원인은?

A3. CSV는 필드 구분 기호나 인코딩 차이로 인해 불필요한 제어 문자가 삽입될 수 있다. TEXTIMPORT 마법사에서 65001 UTF-8 인코딩을 선택하고, 불필요 공백 제거 옵션을 활성화한 뒤 CLEAN()을 추가 적용한다.

Q4. 대소문자를 구분하며 조회해야 할 때는?

A4. VLOOKUP은 기본적으로 대소문자를 구분하지 않는다. INDEX(MATCH())와 EXACT()를 조합하거나, XLOOKUP의 match_mode 인수에 0을 두고 search_mode를 -1로 지정해 대소문자 감별을 구현한다.

Q5. 동적 배열 환경에서 다중 결과를 반환하려면?

A5. FILTER() 함수가 넓은 범위를 반환하므로 이후 INDEX()나 TAKE()로 필요한 열만 추출하면 된다. 배열 스필로 인해 기존 값이 날아가지 않도록 빈 영역에 수식을 입력해야 한다.