[엑셀공부하징] 엑셀 필수기능 vlookup, 일치하는 값 찾을 때, 일치하는 구간 찾을 때 모두 이용 가능한 vlookup

 

 

엑셀에서 가장 많이 쓰는 기능 탑5 안에는 들지 않을까

엑셀에서 서로 다른 곳에 위치한 테이블의 일치하는 값 찾을 때

쓸 수 있는 vlookup을 써보자

활용도가 너무 높은 함수이다

 

그리고 한 걸음 더 보통 일치하는 값 찾을 때만 쓰는 데

일치하는 구간을 찾을 때도 vlookup을 쓸 수 있다

그것까지 함께 알아보자

 

----------------------------------------------------------------

내 맘대로 난이도

(1~5점, 1점에 가까울 수록 쉽고 간단하고, 5에 가까울수록 귀찮고 복잡하다)

 

난이도 : ■■■□□ ( 3점 : 처음엔 좀 까다로울 수 있는데, 계속 쓰다보면 익숙해 진다 )

사용방법 : ■■■□□ ( 3점 : 귀찮지만, 엑셀 키면 무조건 쓰는 거니까 )

활용도 : ■■■■■ ( 5점 : vlookup없는 엑셀은 앙꼬없는 찐빵, 오아시스 없는 사막)

----------------------------------------------------------------

 

vlookup은 언제쓰면 좋을까

 

내 맘대로 난이도에서 말한 것 처럼

vlookup없는 엑셀은 상상하기도 싫다

대부분 잘 알고 있겠지만, 서로 다른 테이블에 있는

일치 하는 값을 찾고자 할 때 쓰면 된다

말로하면 어렵고 예시를 보면 쉽다

 

단촐하지만 훨씬 길고 복잡한 표라고 상상해보자, 쓰고 있는 업무에 대입해봐도 좋다

주문내역표의 단가를 구해 결제가격을 구한다고 생각해보자

가격표에 있는 각 상품의 가격을 가지고 올 수 있다면

훨씬 일이 수월해 질 것이다

물론, 표를 보고 하나하나 옮길 수 있다

우리의 눈과 손은 충분히 할 수 있지만

이게 1,000개 10,000개가 된다고 해보자

8시간 근무 시간 중 4시간은 여기에 할애해야할지도 모른다

우리 제발 그런 비효율적인 일은 하지말자

 

바로 실전! vlookup 이렇게 일치하는 값을 찾아보자

 

 

vlookup은 이렇게 써보면 된다

 

vlookup(대상값, 일치하는값이 있는 표의 범위, 일치하는 값의 열 숫자, 유사일치/정확일치)

대상값 = (주문내역 표의) 아이스크림(빨간박스)
일치하는 값이 있는 표의 범위 = 가격표 범위(파란박스)
일치하는 값의 열 숫자 = 상품가격열 (상품명이 첫번째 열, 상품가격이 두 번째 열이므로 "2"를 쓴다
유사일치/정확일치 = 정확일치(=FALSE =0) 정확하게 일치하는 값을 찾으면 0을쓰면 된다, FALSE입력도 가능!
                            (유사일치 값을 찾으려면(=TRUE = 1)을 쓰면 된다, 0을 대부분 많이 쓴다

vlookup은 생각보다 주의할 점이 많다

 

주의할 점 1번째

아마도 vlookup을 익숙하게 많이 쓰는 사람들도 순간적으로 왜 결과가 안나오지 하는 경우가 있다

표의 범위를 설정할 때가 문제인데,

범위 지정할 때 첫 번째 열을 반드시 일치하는 값이 있는 열로 잡아야 한다

위 표는 상품명과 일치하는 상품가격을 찾는 것이기 때문에 상품명 부터 범위를 잡았다

가격 표에 다른 열이 많았더라도 상품명을 첫번째로 두어야 한다

그래야 그 기준으로 일치하는 첫번째열, 두 번째 열을 찾을 수 있기 때문이다

 

주의할 점 2번째

역시 범위를 지정할 때 많이 발생하는데,

표의 범위를 잡을 때 범위에 절대값($, 요 달러표시)을 씌우지 않고

수식채우기를 해버린다면, 범위 표가 계속 바뀌게 되므로 값이 제대로 안 나올 수 있다

꼭 F4를 눌러서 절대값을 씌우자 (F4가 안먹히면 Fn(펑션키)+F4를 같이 눌러보자)

 

그리고 약간의 팁

우리의 엑셀러들은 다 알겠지만.. 수식 채우기 할때 굳이 마우스로 채우기 핸들을 끌지 않고

수식의 시작 셀과 수식 채우고 싶은 가장 마지막 셀을 쉬프트를 눌러 범위를 잡고

ctrl + D키를 누르면 자동으로 수식이 채워진다 손목보호를 위하여 참고해보자

 

 

한 걸음 더! 유사 일치를 쓰면? 일치하는 구간 값을 찾을 수 있다!

 

vlookup의 응용이 너무 많아서 고민했는데,

일치하는 구간 값을 찾을 때 이걸 쓰면 좋을 것 같아 공유한다

생각보다 얼른 안 떠오르는데 이럴 때도 우리의 친구 vlookup을 쓴다

 

아래 상황처럼 구개 가격 범위별로 등급을 구하고 싶다 고 할 때

vlookup이 얼른 떠오르지 않을 수 있지만,

vlookup으로 구할 수 있다

구매등급구간표에 있는 등급을 주문내역 표의 등급 열에 불러 온다

어떻게 하면 될까?

 

그렇다,

vlookup(가격셀, 구매등급구간범위(절대값!), 2(두번째열이므로), 1(또는 TRUE))를 입력하면된다

 

아래 표에 색을 대입해서 보면 된다,

가격 1500과 일치하는 값은 구매등급구간 표에는 없지만

0과 같거나 크고, 3000보다 작은 구간인 0, 등급없음 구간과 일치하므로 "등급없음" 값을 반환한다

가격 12500의 경우

12000보다 같거나 크고, 15000보다 작은 구간인 vip와 일치하므로 vip값을 반환한다

 

 

vlookup으로 이렇게 구간별 일치값을 단번에 찾아낼 수도 있으니

여러모로 중요한 함수라 할 수 있다

이 뿐만 아니라 응용방법이 무궁무진하므로 잘 활용해보도록 하잣

 

+

혹시 궁금한 함수나

안되는 것이 있으면 댓글로 남겨주시면 포스팅 할 수 있도록 하겠습니다