엑셀 VLOOKUP 함수, ‘N/A’ 오류는 왜 발생할까요?

엑셀을 사용하다 보면 VLOOKUP 함수만큼 유용한 함수도 드물죠. 하지만 이 강력한 함수를 사용할 때 가장 흔하게 마주치는 골칫덩이가 바로 ‘#N/A’ 오류입니다. 대체 이 오류는 왜 나타나는 걸까요?

‘#N/A’ 오류의 주요 원인 파헤치기

‘#N/A’ 오류는 “Not Available”의 약자로, VLOOKUP 함수가 찾고자 하는 값을 찾을 수 없을 때 발생합니다. 즉, 검색하려는 값이 조회 범위에 존재하지 않는다는 신호죠. 좀 더 구체적으로 살펴보면 다음과 같은 이유들이 있습니다.

• 찾는 값이 조회 범위에 없음: 가장 기본적인 원인입니다. 예를 들어, ‘사과’라는 값을 찾으려 하는데, 조회 범위에 ‘사과’라는 텍스트가 없는 경우입니다.

• 데이터 불일치 (띄어쓰기, 오탈자): 눈으로는 비슷해 보이지만, 실제로는 미세한 차이가 존재할 때 오류가 발생합니다. 가장 흔한 경우가 바로 띄어쓰기 오류입니다. 예를 들어, “서울시”와 “서울 시”는 엑셀에게는 전혀 다른 값으로 인식됩니다. 또한, “고객명”을 찾는데 “고객명 ” (뒤에 띄어쓰기 포함) 또는 “고객명” (띄어쓰기 없음) 처럼 미세한 차이가 있어도 오류가 납니다.

• 데이터 형식 불일치 (숫자 vs 텍스트): VLOOKUP 함수는 찾는 값과 조회 범위의 값이 같은 형식일 때만 제대로 작동합니다. 예를 들어, 찾으려는 값이 숫자 123인데, 조회 범위에 있는 해당 값이 텍스트 “123”으로 저장되어 있다면 오류가 발생합니다. 반대의 경우도 마찬가지입니다.

• 조회 범위 설정 오류: VLOOKUP 함수에서 col_index_num (몇 번째 열의 값을 가져올지 지정하는 인수)이나 range_lookup (정확히 일치하는 값을 찾을지, 근사값을 찾을지 지정하는 인수)의 설정이 잘못되었을 때도 의도치 않은 결과를 얻거나 오류가 발생할 수 있습니다. 특히 range_lookup을 TRUE (근사값)로 설정했는데, 정렬되지 않은 데이터에 적용하면 예상치 못한 결과가 나올 수 있습니다.

이러한 원인들을 명확히 이해하는 것이 ‘#N/A’ 오류를 해결하는 첫걸음입니다.

엑셀 VLOOKUP 오류 ‘N/A’ 해결하는 3가지 핵심 방법

이제 ‘#N/A’ 오류의 원인을 파악했으니, 본격적으로 해결 방법을 알아보겠습니다. 이 세 가지 방법만 잘 익혀두면 대부분의 VLOOKUP 오류는 해결할 수 있습니다.

1. 찾는 값과 조회 범위의 데이터 일치 확인하기

가장 먼저 의심해봐야 할 부분은 바로 데이터 자체의 일치 여부입니다. 눈으로는 같아 보이지만, 엑셀은 아주 작은 차이도 놓치지 않습니다.

1.1 띄어쓰기 및 공백 문자 제거

말줄임표(…)나 눈에 보이지 않는 공백 문자가 원인일 가능성이 높습니다.

• 방법:

• 찾는 값 셀과 조회 범위의 해당 열을 모두 선택합니다.

• Ctrl + H (찾기 및 바꾸기)를 실행합니다.

• ‘찾을 내용’에 스페이스바를 한 번 눌러 띄어쓰기를 입력합니다.

• ‘바꿀 내용’은 비워둡니다.

• ‘모두 바꾸기’를 클릭합니다.

• 만약 여러 개의 띄어쓰기가 붙어 있다면, 이 과정을 여러 번 반복하거나, TRIM 함수를 활용하여 텍스트 앞뒤의 불필요한 공백을 제거할 수 있습니다. 예를 들어, TRIM(A1)과 같이 사용하면 A1 셀의 텍스트에서 앞뒤 공백과 단어 사이의 여러 공백을 하나로 줄여줍니다.

1.2 오탈자 및 유사 문자 확인

‘서울시’와 ‘서울시’ (한글 자음만 다름), ‘100’과 ‘100 ‘ (뒤에 공백) 등 미묘한 차이를 점검해야 합니다.

• 방법:

• 찾는 값과 조회 범위의 데이터를 육안으로 비교하며 오탈자를 찾습니다.

• 필터 기능을 활용하여 조회 범위의 특정 열을 필터링한 후, 찾는 값과 일치하는지 하나씩 확인해볼 수 있습니다.

• COUNTIF 함수를 사용하여 찾는 값이 조회 범위에 몇 개 있는지 확인해보는 것도 좋습니다. 예를 들어, COUNTIF(조회범위, 찾는값) 결과가 0이면 해당 값은 존재하지 않는 것입니다.

2. 데이터 형식 통일하기 (숫자 vs 텍스트)

VLOOKUP 함수는 찾는 값과 조회 범위의 데이터 형식이 다르면 오류를 반환합니다. 특히 숫자와 텍스트 형식이 섞여 있을 때 자주 발생합니다.

2.1 숫자 형식 확인 및 변환

조회 범위에 숫자가 텍스트로 저장되어 있거나, 반대로 찾는 값이 숫자가 아닌 텍스트로 입력된 경우입니다.

• 방법:

• 텍스트 형식의 숫자를 숫자 형식으로 변환:

• 숫자로 인식되지 않는 셀(보통 왼쪽 상단에 녹색 삼각형 표시가 됨)을 선택하고, 나타나는 오류 아이콘을 클릭합니다.

• ‘숫자로 변환’ 옵션을 선택합니다.

• 또는, 해당 열을 선택하고 Ctrl + 1을 눌러 셀 서식을 ‘숫자’ 또는 ‘일반’으로 변경합니다.

• VALUE 함수를 사용할 수도 있습니다. VALUE(텍스트_숫자) 형태로 사용하면 텍스트 형식의 숫자를 실제 숫자 형식으로 변환해 줍니다.

• 숫자 형식의 텍스트를 텍스트 형식으로 변환:

• 해당 열을 선택하고 Ctrl + 1을 눌러 셀 서식을 ‘텍스트’로 변경합니다.

• TEXT 함수를 사용할 수도 있습니다. TEXT(숫자, "형식") 형태로 사용하면 숫자를 지정한 형식의 텍스트로 변환합니다. 예를 들어, TEXT(A1, "0")은 A1 셀의 숫자를 텍스트 형식의 숫자 “0”으로 변환합니다.

2.2 데이터 형식을 일관되게 관리하는 습관

데이터를 입력하거나 가져올 때부터 형식을 통일하는 것이 가장 좋습니다.

• 방법:

• 데이터를 입력할 셀의 서식을 미리 ‘텍스트’ 또는 ‘숫자’로 지정해 둡니다.

• 외부 데이터를 가져올 때는 ‘텍스트 가져오기 마법사’를 활용하여 각 열의 데이터 형식을 명확하게 지정합니다.

3. VLOOKUP 함수의 인수 재확인 및 수정

VLOOKUP 함수의 네 가지 인수(lookup_value, table_array, col_index_num, range_lookup)가 올바르게 설정되었는지 점검해야 합니다.

3.1 lookup_value (찾는 값) 오류

찾는 값이 잘못 지정되었거나, 예상치 못한 값이 들어갈 경우 오류가 발생할 수 있습니다.

• 방법:

• VLOOKUP 함수에서 참조하는 lookup_value 셀이 올바른지 확인합니다.

• 만약 lookup_value가 다른 함수나 수식의 결과라면, 해당 결과 값이 의도한 대로 나오는지 먼저 점검합니다.

3.2 table_array (조회 범위) 오류

조회 범위가 너무 좁거나, 절대 참조($)가 제대로 설정되지 않아 발생할 수 있습니다.

• 방법:

• VLOOKUP 함수에서 지정한 table_array 범위가 찾는 값과 가져올 값이 모두 포함하는지 확인합니다.

• 만약 VLOOKUP 함수를 복사하여 여러 셀에 적용할 계획이라면, table_array 범위를 절대 참조($)로 고정해야 합니다. 예를 들어, A1:D10 대신 $A$1:$D$10 과 같이 사용합니다. F4 키를 누르면 쉽게 절대 참조로 변경할 수 있습니다.

3.3 col_index_num (가져올 열 번호) 오류

가져올 열 번호가 조회 범위의 실제 열 번호와 일치하지 않거나, 범위를 벗어나는 경우입니다.

• 방법:

• table_array의 첫 번째 열을 1로 시작하여, 가져오고 싶은 값이 있는 열의 번호를 정확하게 입력합니다.

• 예를 들어, table_array가 $A$1:$D$10이고, B열의 값을 가져오고 싶다면 col_index_num은 2가 됩니다.

3.4 range_lookup (값의 정확성 여부) 오류

이 인수는 TRUE (근사값, 내림차순 정렬 필수) 또는 FALSE (정확히 일치)로 설정됩니다.

• 정확히 일치하는 값을 찾고 싶을 때: 반드시 FALSE 또는 0을 입력해야 합니다. 만약 데이터에 정확히 일치하는 값이 없으면 ‘#N/A’ 오류가 발생합니다.

• 근사값을 찾고 싶을 때: TRUE 또는 1을 입력합니다. 이 경우, table_array의 첫 번째 열은 반드시 오름차순으로 정렬되어 있어야 합니다. 정렬되지 않은 데이터에 TRUE를 사용하면 잘못된 결과를 얻거나 ‘#N/A’ 오류가 발생할 수 있습니다.

• 흔한 실수: 많은 사용자들이 range_lookup 인수를 생략하는데, 이 경우 기본값은 TRUE로 설정됩니다. 따라서 정확히 일치하는 값을 찾으려면 반드시 FALSE 또는 0을 명시해야 합니다.

엑셀 VLOOKUP 오류 ‘N/A’를 똑똑하게 처리하는 IFERROR 활용법

앞서 살펴본 방법들로 ‘#N/A’ 오류를 해결할 수 있지만, 때로는 오류가 발생하는 것이 불가피하거나, 오류 메시지를 사용자 친화적으로 바꾸고 싶을 때가 있습니다. 이때 유용하게 사용할 수 있는 함수가 바로 IFERROR입니다.

IFERROR 함수란 무엇인가?

IFERROR 함수는 첫 번째 인수(value)에 지정된 수식이나 값이 오류를 반환할 경우, 두 번째 인수(value_if_error)에 지정된 값을 대신 표시해주는 함수입니다. 만약 첫 번째 인수가 오류가 아니라면, 원래의 수식이나 값이 그대로 표시됩니다.

IFERROR(수식, 오류일 때 표시할 값)

IFERROR 함수를 VLOOKUP과 함께 사용하는 방법

IFERROR 함수를 VLOOKUP 함수와 결합하면 ‘#N/A’ 오류가 발생했을 때 원하는 메시지나 값을 표시할 수 있습니다.

1. 오류 발생 시 특정 텍스트 표시하기

예를 들어, VLOOKUP 함수로 데이터를 찾지 못했을 때 “데이터 없음” 또는 “찾을 수 없음”과 같은 메시지를 표시하고 싶을 때 사용합니다.

• 예시: =IFERROR(VLOOKUP(A2, Sheet2!$A$1:$B$10, 2, FALSE), "데이터 없음")

• 이 수식은 VLOOKUP(A2, Sheet2!$A$1:$B$10, 2, FALSE) 함수가 ‘#N/A’ 오류를 반환하면 “데이터 없음”이라고 표시하고, 오류가 아니라면 VLOOKUP 함수의 결과 값을 그대로 표시합니다.

2. 오류 발생 시 빈 셀로 표시하기

오류 메시지가 지저분하게 보이는 것을 원치 않을 때, 빈 셀로 처리하는 것이 깔끔합니다.

• 예시: =IFERROR(VLOOKUP(A2, Sheet2!$A$1:$B$10, 2, FALSE), "")

• "" (빈 따옴표)는 빈 텍스트를 의미합니다. 이 수식을 사용하면 ‘#N/A’ 오류 대신 빈 셀로 표시됩니다.

3. 오류 발생 시 특정 숫자나 값으로 대체하기

찾지 못했을 때 0이나 다른 기본값으로 대체하고 싶을 때 사용할 수 있습니다.

• 예시: =IFERROR(VLOOKUP(A2, Sheet2!$A$1:$B$10, 2, FALSE), 0)

• 이 경우, VLOOKUP 함수가 ‘#N/A’ 오류를 반환하면 0이라는 숫자가 표시됩니다.

IFERROR 함수 사용 시 주의사항

• 모든 오류를 덮어쓰지 않도록 주의: IFERROR 함수는 첫 번째 인수에 지정된 수식에서 발생하는 모든 종류의 오류를 처리합니다. 따라서 VLOOKUP 함수 자체의 잘못된 인수 설정이나 다른 로직 오류로 인해 발생하는 오류까지 ‘오류일 때 표시할 값’으로 덮어쓸 수 있습니다. VLOOKUP 함수의 논리적 오류는 별도로 점검해야 합니다.

• 디버깅의 어려움: IFERROR 함수로 오류를 덮어버리면, 실제 문제의 원인을 파악하기 어려워질 수 있습니다. 따라서 최종 보고서나 사용자에게 보여줄 때만 IFERROR를 사용하고, 데이터를 분석하거나 수정할 때는 IFERROR를 제거하고 원래의 VLOOKUP 함수만 사용하는 것이 좋습니다.

결론: ‘N/A’ 오류, 이제 두렵지 않아요!

엑셀 VLOOKUP 함수 사용 시 발생하는 ‘#N/A’ 오류는 많은 사용자들에게 좌절감을 안겨주지만, 오늘 배운 세 가지 해결 방법과 IFERROR 함수 활용법을 익히면 충분히 극복할 수 있습니다.

핵심 요약 및 실행 액션

1. 데이터 검토: 띄어쓰기, 오탈자, 데이터 형식(숫자/텍스트) 불일치를 꼼꼼히 확인하고 수정하세요. TRIM, VALUE, TEXT 함수가 도움이 됩니다.

2. VLOOKUP 인수 점검: lookup_value, table_array (절대 참조 필수!), col_index_num, range_lookup (특히 FALSE 사용!) 인수가 올바르게 설정되었는지 재확인하세요.

3. IFERROR 함수 활용: 오류 발생 시 사용자 친화적인 메시지(“데이터 없음”, “”)나 특정 값(0)으로 대체하여 깔끔하게 처리하세요.

이 팁들을 꾸준히 연습하시면, 엑셀 VLOOKUP 함수를 더욱 자신감 있게 활용하실 수 있을 것입니다.