엑셀(excel) - VLOOKUP 함수 기본 사용법과 오류 정리

엑셀에서 VLOOKUP 함수를 잘 이용하면 의외로 많은 문제들이 해결된다.

특히 두 개의 테이블을 연결한다는 점에서 후에 알게 된 관계형데이터베이스에서 JOIN개념을 이해하는 데도 큰 도움이 되었다.

 

그래서 VLOOKUP 함수의 원리만 이해한다면 엑셀에서 다른 함수나 기능을 익히는데 진입장벽을 많이 낮출 수 있다.

 

 

VLOOKP 함수의 기본 개념

배열의 첫 열에서 값을 검색하여, 지정한 열의 같은 행에서 데이터를 돌려줍니다. 기본적으로 오름차순으로 표가 정렬됩니다.

G5 셀의 값이 도출되는 과정은 다음과 같다.

① F5 셀 값이 B4:D10 범위에서 정확하게 일치하는 값을 첫번째 행부터 아래로 내려가면서 찾는다. 

② 찾은 값의 행 번호를 저장한다. (여기서는 세번째 행이 된다.)

③ 해당 행번호에 맞는 '3'번째 열의 값을 돌려준다.

 

물론 상기 모든 과정은 엑셀이 처리하는 방식이고, 사용자는 결과값만 볼 수 있다.

 

 

해당 과정을 이해했다면, 몇 가지 주의사항이 발생하게 된다.

 

① 반환되는 값은 반드시 찾으려는 값에서 오른쪽에 위치하여야 한다.

• 반환할 열번호는 찾으려는 값에서 오른쪽으로 얼마만큼 떨어졌는지 표현하는 것이기 때문이다.
• 반환할 열번호는 음수가 불가능하다.

② 찾으려는 값은 반드시 고유값이어야 한다.

• 찾으려는 값이 찾으려는 범위에서 중복값이 존재한다면, 첫번째로 일치하는 값을 저장한다.
• 따라서 중복값 중 가장 위쪽에 위치한 값이 반환된다.

참고로 해당 주의사항은 office365의 XLOOKUP이라는 함수로 해결이 가능하다.

 

VLOOKP 함수의 자주하는 실수와 오류

몇 가지 주의사항을 제외하고도 종종 발생하는 실수들이 있다.

 

1.  찾으려는 값에 공백이 포함되어 있을 때

겉보기엔 이상없지만, 사실은 '가'항목은 '가 (✓)'으로 기재되었다.

클랜징이 완벽하게 되어 있지 않은 데이터에서 많이 발견되는 실수이면서 오류다.

결국엔 찾으려는 값과 완전히 일치하지 않기 때문에 '사용할 수 없는 값'이라는 의미의 #N/A 오류를 출력한다.

보통은 사용자가 함수를 제대로 입력을 하지 않았다고 생각하기 때문에 빨리 깨닫기 어려운 실수 중 하나라고 생각한다.

 

해결하는 방법은 데이터를 가공하기 전에 ctrl+H(찾아바꾸기)를 통해 공백을 제거하는 습관을 가지는 것이다.

찾을 내용에 공백을 넣고 '모두 바꾸기'를 눌러준다.

특히나 데이터셋의 출처가 불분명할 때, 전처리가 필요한 데이터를 다룰때는 이러한 습관이 실수를 줄일 수 있다.

 

 

2. 숫자로 지정된 텍스트 데이터를 찾을 때

다른 함수를 적용할 때도 종종 나오는 실수 중 하나다.

엑셀 뿐만 아니라 데이터의 형식은 중요한데, 눈으로 볼 때는 숫자지만 컴퓨터가 인식하는 형식은 텍스트다.

그래서 역시 찾으려는 값과 일치하지 않는다고 판단한다.

마찬가지로 눈으로 볼 때는 구별하기 힘들다. 대신 형식이 맞지 않는 데이터는

이렇게 친절하게 힌트를 알려준다.

데이터 형식만 맞춰주면 되는데 이 경우에는 찾으려는 값을 텍스트 형식으로 찾거나, 찾는 값을 숫자로 바꿔주면 된다.

즉, 찾으려는 범위의 값이 텍스트라면 'VLOOKUP("1",$C$6:$D$10,2,FALSE)' 강제로 바꿔주는 것도 해결방법이 될 수 있다.

 

마무리

아직도 대부분의 작업은 VLOOKUP만 잘 해도 반은 먹고 들어간다.

특히, VLOOKUP으로 데이터의 확장성을 엄청나게 높일 수 있다. 

 

다음 포스팅에서는 VLOOKUP의 다양한 활용법을 알아보자.