vlookup함수 범위에서 값 찾기, 옵션 값 활용하기

 

오랜만에 엑셀 포스팅!

보통 vlookup이라고 하면 꼭 일치하는 값을 찾을 경우 쓰는 경우가 많다. 사실 그 때 쓰기만 해도 아주 훌륭하긴 하다! 그렇지만, 이런 경우에는 어떻게 할까 고민하다가 수식에 수식을 덧씌워쓰거나, 중간 가공을 또 거쳐서 값을 뽑기도 하는데 이런 복잡한 절차 없이 vlookup으로도 해결가능하다는 사실!

 

vlookup 범위값 찾기가 필요한 경우

그러니까 이런 경우 말이다.

- A. 잔여 재고량이 따라서 할인율을 다르게 부여해야 하는 case. 재고량이란 이렇게 정책처럼 딱 맞아 떨어지지 않을 것이다.

- B. 성과 점수에 따라서 등급을 부여해야 하는 case.

- C. 임금구간별 세율을 알아보아야 할 때 case.

대표 예시이지만, 기본 개념만 알아 둔다면 다양한 예시에서 활용할 수 있을 것이다. 우리는 아래 기준을 가지고 아래 노란셀 영역을 구하고자 한다. 예시 정도의 갯수로는 조금 수고스럽지만 눈으로 하나하나 맞춰볼 수도 있을 것이다. 그렇지만 우리는 몇 십개가 아니라 수천, 수만개의 행을 다루는 작업자가 아닌가. 하나하나 다 하다간 밤을 새도 모자랄 것이다.

물론 여러 다른 수식을 쓰는 방법도 있지만, 깔끔하게 vlookup을 써보자.

 

 

Vlookup 옵션 값을 다르게 선택할 수 있다는 사실! 

vlookup 좀 써본 사람이라면, 검색할 값을 입력하고, 찾아올 값의 범위를 입력하고, 열의 순서를 입력하고(=열 인덱스번호), 그리고 신나게 <0>을 누르고 괄호를 닫을 것이다. 아주 오래전부터 우리의 손가락에 각인된 습관 같은 것이라 의심의 여지가 없었던 것인지도 모른다.

 

그러나 0을 선택한다는 것은 아래 옵션에서 FALSE - 정확히 일치를 선택한다는 의미이고, 그럼 어떻게 될까?

그렇다 n/a파티를 하게 된다. 엑셀 작업하다 가장 맞닥뜨리기 싫은 순간을 맞이하게 되는 것이다. 으아. 보기만 해도 가슴이 답답하다.

해결방법은 간단하다. 0말고 다른 숫자를 쓰면 된다.

 

0자리에 1을 써주기만 하면 짜잔! 이렇게 기준에 있는 대로 제 값을 찾아간다. (사실 0이 아닌 모든 값이 다 가능한데, 편의상 1이라고 알아두자. 0은 꼭 맞는 값, 1은 범위에 해당하는 값)

즉 상품 재고가 345개 남아있으면, 기준 재고수량 표를 보면 300개대일때는 20% 할인율을 주라고 했는데 맞게 나왔고

재고량 784개는 기준 재고수량인 500개보다 많으니까 40%의 할인율이 맞게 들어갔다.

여전히 #n/a가 남아 있는게 맘에 걸린다고? 고건 아래 심화학습에서 알아보자. 그 전에 한 번 정리하고 넘어가자.

 

범위에서 값을 찾을 때는 옵션 값을 0말고 1을 써주자.

정리 페이지니까 요약을 하면 이렇다.

vlookup이라는 함수는 이렇게 구성되어 있다. 아래 예시에서 색별로 매칭해보면 이런 의미다.

=vlookup(검색할 값, 표_범위, 열_인덱스번호, 옵션)

* 검색할값 : (파랑) 아래예제에서 77점일 경우 어떤 등급을 부여할지 찾는 것이므로, 주인공 즉 검색할 값이 된다.
* 표_범위 : (빨강) 어떤 등급을 부여할지 기준표의 "범위"를 입력해야 한다. 아래에서는 빨간색 영역이다.
* 열_인덱스번호 : (초록) 반환하고 싶은 값이 있는 열의 순서다. 등급은 두번째 열에 있으니까 2로 기재되어 있다.
* 옵션 : (보라) 오늘 같이 스터디 한 것!!! 0을 쓰면 정확히 일치하는 값, 1을 쓰면(!!!) 범위에 맞는 값을 가져온다.

옵션 FALSE, 1의 의미 :
정확히는 검색할 값보다 작거나, 가장 가까운 값을 찾아준다. 즉 77의 경우 기준표에서 <작거나>에 해당하는 건 70과 50이 되고, <가장 가까운>에 해당하는 것은 70이 되어서 C값을 반환한다. 85도 가까운 값이긴 하나 기준값보다 작지 않으므로 반환하지 않는 것이다.

 

 

자 그렇게 우리가 배운 것을 모든 예제에 적용해주면 짜잔 이렇게 된다!

 

심화학습. 그래도 남아 있는 #N/A는 어떻게 하나?

어떻게 처리할지 아는 분들께서는 이제 떠나주셔도 됩니다. 잘 모르겠는 분 같이 더 들여다봐요.

A,B,C 예제에서 C 케이스는 n/a값이 안생겼다. 왜? 눈썰미가 있다면 눈치 챘을 것이다. 기준값에 0일 경우인 case가 있어서 (세율 5% 구간) 옵션_false 정의에 따라 "작거나" "가까운" 값을 충족해서 민수의 경우처럼 월급이 220만원이라 기준 구간이 300만원 부터라면 "작거나"의 case가 없어서 n/a값이 떴겠지만 0이 있어서 모든 값이 다 떴다는 것을!

그러면 떠오르는 방법은 하나. A, Bcase에도 열 추가를 해서 0일 때 할인율과 등급을 넣어주면 된다. 

 

그럼 끝...? 이라기엔 좀 아쉬우니까 하나 더 소개해보자. iferror를 쓰면된다.

이 수식은 문자 그대로 if만약에 (이 수식이) error라면, 이 값을 반환해줘 라는 의미이기 때문에

=iferror(에러여부를 확인할 수식, 에러일 경우 노출할 값)

=iferror(vlookup(기존수식), "할인없음") 이런식이라고 쓰면된다

 

자 그렇담 이 절차를 모두 맞치면 이렇게 짠 하고 결과가 나온다!!

 

 

마지막으로 복습하면, 범위 값을 찾아서 매칭하고 싶을 때는

vlookup쓰고 마지막으로 습관적으로 0을 쓰지말고 1을 쓰자!! 요약 끗!

또 만나요 제발!