"vlookup 쓸 줄 알아?" 회사에서 직원 엑셀 실력을 가늠할 때 하는 대표적 질문입니다. 실제로 vlookup 정도만 제대로 사용해도 엑셀 초보는 벗어날 수 있는데요. 오늘은 vlookup 함수 사용법을 정리해봤습니다.
vlookup 함수를 어떻게 사용하는지는 물론, 함수 사용시 주의사항과 주요 오류 해결법까지 알려드리겠습니다.
vlookup 함수 사용법
vlookup은 데이터 안에서 특정한 열에 매칭되는 값을 불러오고 싶을 때 사용합니다. 예를 들어서, 전교생의 시험성적이 기록된 데이터 안에서 홍길동(찾는값)의 수학점수(열번호)를 찾고 싶을 때 이용하는 식이죠.
말로만 설명하면 헷갈리니 아래 예제파일을 통해서 자세한 vlookup 함수 사용법을 실습해보겠습니다.
아래와 같이 모델명, 상품명, 가격, 전시위치 값이 정리된 데이터가 있고 모델명을 입력하면 상품명~전시위치가 자동으로 입력되도록 만들고 싶습니다. 우선 ①찾는값 ②찾을범위 ③열번호 ④일치여부 이 4개의 명칭을 잘 기억하세요.
C3 셀에 우선 vlookup 함수를 입력한 뒤 ①찾는값(모델명 → B3) 셀 선택, ②찾을 범위(B6~E10) 선택, ③열번호 (모델명(B열)에서 몇 번째 열인지), ④일치여부(TRUE / FALSE)를 차례대로 입력합니다.
이 경우 상품명이 모델명 바로 옆 열이기 때문에 ③열번호에는 숫자 '2'를 입력합니다. 일치여부는 TRUE(혹은 숫자 1)나 FALSE(혹은 숫자 0)를 입력하면 되는데, 특별한 경우가 아니면 FALSE(0)을 입력해 줍니다.
이렇게 'SK342' 모델명에 해당되는 상품명(삼성 통돌이세탁기)이 자동으로 입력됩니다. 마찬가지로 'D3' 셀에는 모델명에 해당되는 가격 값을 불러와 보겠습니다.
다른 수식은 동일하게 작성하되 ③열번호만 '3'으로 입력하면 되겠죠? '가격'열이 '모델명'에서 오른쪽으로 3칸 옆이니까요. 마찬가지로 'E3'셀 '전시위치'에는 ④열번호를 4로 입력하면 됩니다.
참고로 vlookup 함수를 사용할 때 로데이터가 다른 sheet에 있더라도 범위 지정이 가능합니다. 범위 지정할 때 데이터가 있는 sheet를 클릭해서 범위를 지정하고 수식을 완성하면 되는데요. sheet2에 있는 범위를 참조한 경우 수식에는 아래와 같이 표시됩니다.
vlookup 함수 사용시 주의사항
1. 절대 참조
vlookup 함수는 표 안에서 복사 붙여넣기를 해야하는 경우도 많습니다. 그런 경우 반드시 ②찾을 범위에 절대참조를 씌워줘야 합니다. 그림과 같이 그냥 범위를 지정하면 밑으로 복사할 때 범위가 한칸씩 밀리면서 오류가 발생합니다.
절대 참조는 ②찾을 범위를 마우스 드래그로 지정한 뒤 "F4"키를 누르면 됩니다. 범위가 지정된 셀 앞뒤에 "$" 표시가 붙은 게 절대 참조라는 뜻입니다.
2. 일치여부 : FALSE (혹은 0)
특별한 경우가 아니라면 ④일치여부에는 FALSE(혹은 0)을 입력하는 게 좋습니다. 그렇지 않으면 ①찾는값이 정확하지 않아도 비슷한 값으로 찾아주거든요. 오타를 쳐도 엉뚱한 값이 반환될 수 있어요.
그런데 이걸 이용할 수 있는 경우도 있습니다. 아래처럼 금액 구간에 따른 할인율을 vlookup을 이용해 F열에 불러오고자 합니다. 이런 경우 가격(D열)이 ①찾는값이 되어야 하는데, 정확히 일치되는 데이터를 만들기 어렵겠죠?
따라서 아래와 같이 최소금액이라는 열을 만들어 입력해주고, 최소금액에서 할인율 까지를 ②찾을 범위로 지정합니다. 그리고 수식에 일치여부에 TRUE(혹은 1)를 입력합니다.
그러면 위와 같이 해당 금액 구간에 맞는 할인율 값을 불러오게 됩니다. 일치여부에 TRUE를 입력하면 ①찾는값과 동일한 값이 ②찾을 범위에 없을 경우, 초과하지 않는 선에서 유사값을 찾게 되거든요.
삼성 무선청소기의 경우 ①찾는값 353,000원이 ②찾을 범위에 없기 때문에 유사값인 0원을 찾아가서(500,000원은 찾는값 초과) 할인율 10%를 불러오는 겁니다.
이때 한가지 주의할 것은 일치여부에 TRUE를 사용할 경우, 데이터가 ②찾을 범위 첫번째 열(최소금액) 기준으로 오름차순 정렬이 되어 있어야 합니다.
3. 왼쪽으로는 조회 불가능
혹시 vlookup 함수 사용할 때 ③ 열번호에 (-)를 입력하면 찾을값 왼쪽에 있는 데이터도 불러올 수 있지 않을까 생각할 수도 있을텐데요. 아쉽지만 vlooup 함수는 위에서 아래로, 왼쪽에서 오른쪽으로만 탐색이 가능합니다.
이런 기능이 필요하다면 xlookup 함수를 이용해야 하는데요. xlookup 함수 사용법은 기회가 된다면 다음에 별도로 포스팅해보도록 하겠습니다.
자주 발생하는 오류
1. #N/A
①찾는값이 ②찾을 범위 첫번째 열에 없는 경우 나오는 오류입니다. 위 예제의 경우 입력한 모델명이 데이터에 없거나 범위지정을 잘못한 경우 이런 메시지가 뜨겠죠.
2. #REF!
② 찾을 범위 넓이보다 큰 ③열번호를 입력하면 나오는 오류입니다. 위 예제의 경우 ③열번호에 '5' 이상을 입력하면 이런 오류가 뜨게 됩니다.
3. #NAME?
이 오류는 보통 수식을 잘못 입력한 경우 발생합니다. 함수명이 제대로 입력되었는지, 따옴표나 괄호가 제대로 입력되었는지 확인해 보세요.
4. #VALUE!
③열번호에 텍스트, 혹은 음수를 입력하면 발생하는 오류입니다. ①찾는값의 글자길이가 255자를 초과해도 이 오류가 뜹니다.
지금까지 엑셀 vlookup 함수 사용법과 주의사항 및 자주 발생하는 오류에 대해 알아봤습니다. 도움이 되셨길 바라며 포스팅을 마치겠습니다.