엑셀 VLOOKUP 함수 오류 예시 해결 방안

오늘은 엑셀 VLOOKUP 함수 오류 해결 방법에 대해 알려드리겠습니다.

지난 번 VLOOKUP 함수를 언제, 어떻게 이용하는 지 알려드리면서 발생할 수 있는 #N/A 오류에 대해 포스팅한 적이 있는데요.

이번 포스팅에서는 #N/A 외에도 vlookup 함수에서 발생할 수 있는 5가지 오류의 원인과 해결방법을 준비해보았습니다.

엑셀 VLOOKUP 함수 오류 5가지

함수 식을 이용할 때 발생하는 오류는 ①#N/A②#REF!③#VALUE!④#NAME?⑤#SPILL!이 있습니다.

#N/A오류는 지난 포스팅에서 문제 원인과 해결 방법을 다루었으니 아래에서 확인하시면 됩니다. 그러면 바로 #REF! 오류 부터 시작해보겠습니다.

오류 예시 & 해결 방법

1. #REF! 오류

#REF! 오류는 col_index_num이 잘못되었을 경우 나타나는 오류입니다.

VLOOKUP 함수식은 다음과 같습니다. =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]

그렇다면 col_index_num을 어떻게 사용하였을 때 오류가 나는 지 예시를 통해 보겠습니다.

저는 vlookup 함수를 =VLOOKUP("M005", A2:C5, 5, FALSE)로 입력하였습니다.

여기서 col_index_num은 '5'가 되는데 데이터 범위를 살펴보면 열은 A,B,C 3개의 열만 존재하기 때문에 5는 데이터 범위에서 벗어납니다.

따라서 문제를 해결하기 위해서는 col_index_num이 table_array 범위에 있는 열 내에 존재하는 지 다시한번 확인해야 합니다.

만약 col_index_num을 데이터 범위 내에 있는 열 '3'으로 변경할 경우 #REF! 오류 없이 6000이 나오는 것을 확인할 수 있습니다.

2. #VALUE! 오류

다음은 #VALUE! 오류의 원인과 해결 방법에 대해 알려드리겠습니다.

#value!는 아래 보이는 것처럼 vlookup 함수를 사용했을 때 결과값이 ##### 이런 식으로 나오게 됩니다.;

이렇게 나오는 이유는 lookup_value나 col_index_num에 오류가 생겼기 때문입니다. 이해하기 쉽도록 위의 예시 데이터를 살펴보겠습니다.

예시에 입력된 함수식을 보면 =VLOOKUP("MOO2", A2:C5, "두번째열", FALSE) 이렇게 되어있습니다.

여기서 col_index_num이 숫자 형식이 되어야 하는데 텍스트 형식으로 되어있기 때문에 문제가 발생한 것입니다.

이 경우에는 잘못된 형식을 다시 숫자 형식으로 올바르게 변경하면 문제가 해결됩니다.

3. #NAME? 오류

#NAME? 오류는 VLOOKUP 함수 이름이나 범위가 잘못되었을 때 발생하는 오류입니다.

아래 VLOOKUP 함수식을 잘 보면 바로 실수를 확인할 수 있는데요. 함수이름이 VLOOKUP이 아닌 VLOKUP으로 오타가 나있습니다.

#NAME? 오류는 함수 이름을 잘못 쓰거나 혹은 table_array에 정의되지 않은 범위의 이름을 사용할 경우에 발생합니다.

오류를 해결하려면 VLOOKUP의 함수 이름이 제대로 입력되었는지 확인해보거나 table_array에 정의되지 않은 범위 이름이 사용되었는지 확인 후 변경하시면 됩니다.

4. 동적 오류 (#SPILL!)

마지막으로 #SPILL! 오류에 대해서 말씀드리겠습니다.

이 오류는 사실 VLOOKUP 함수식의 오류라기 보다는 엑셀의 동적 배열 기능에서 발생되는 오류입니다.

예를 들어 함수식에 =VLOOKUP("MOO2", A2:C5, 2, FALSE)를 입력해보겠습니다.

그러면 제 엑셀 데이터에서는 오류없이 '키위'가 나옵니다. 하지만 반환 범위가 충돌할 경우에는 #SPILL! 오류가 발생할 수 있습니다.

동벅 배열 기능이란 엑셀에서 여러 값을 한 번에 반환하는 기능으로 최신 엑셀 버전에서 지원되는 기능입니다. 따라서 이 오류를 없애려면 수식이 반환하는 값의 범위가 기존의 데이터와 충돌하지 않는 지 확인해야 합니다.

이상으로 지금까지 엑셀 VLOOKUP 함수 오류 원인과 해결 방안에 대한 포스팅을 마치도록 하겠습니다.