[구글 스프레드시트] vlookup의 한계 - index match 중첩

 

 

vlookup 의 한계? index match 중첩으로 해결!

 

 

 

이번 post 는 좀 복잡할 수 있으니 침착하게 찬찬히 읽으시길 권장드립니다.

but, 한번 이해하고 익숙해지면 엑셀신 보장 (짝짝짝)

 

 

 

 

* vlookup의 한계 : 검색하고자 하는 키 왼쪽에 있는 데이터들은 가져올 수가 없다.

 

 

 

예를 들어보겠습니다. 

아래 주문 테이블에서 "티 스토리 볼펜 세트" 라는 단어를 

왼쪽 DB 테이블에서 검색해서 상품코드인 "Ts-penSet" 를 가져다가 H3 셀에 입력해 주고 싶을 때, 

vlookup으로 가능할까요? 

 

 

 

 

 

vlookup 을 사용하면 값을 찾을 수 없다고 합니다

 

 

 

 

 

 

이유는? 

<찾고자하는 키>가 검색할 범위에 제일 왼쪽에 있어야하기 때문이죠. = 범위의 첫번 째 열에 있어야 함.

즉, 위에 DB 테이블처럼 상품명을 검색해서 상품명 왼쪽에 있는 상품코드를 반환해 주고 싶을때는 vlookup을 사용 할 수 없다는 말입니다.

 

이럴 때 혜성처럼 등장하는 함수가 있으니...

바로 index, match 두 함수입니다.

이 두함수를 중첩해서 사용하면,  범위에서 검색하는 키 왼쪽에 있는 데이터도 가져올 수 있습니다. 

 

 

 

자 일단 결과부터 보여드릴게요.  짜잔~   (사진 클릭해서 보면 잘 보여요)

 

 

 

 

 

 

주문 테이블 G3 "티 스토리 볼펜 세트" 상품명을 DB 테이블에서 검색해서

상품명 왼쪽열에 있는 상품코드를 반환하는데 성공했습니다.

 

 

 

 

이제 하나씩 뜯어서 설명해드릴게요.  index 함수와 match 함수 둘 다 알아야겠죠?  

 

 

 

1. 일단 match 함수는,

검색하고자 하는 키가 검색 범위에서 몇번째 행에 있는지를 숫자로 알려주는 함수에요.

즉, 위치를 숫자로 반환해주는 함수죠. 

 

예를 들어서,  주문 테이블  "티 스토리 볼펜 세트" 가 왼쪽 DB 테이블에 몇 번 째 행에 있는지!

match 함수를 써서 확인해 볼게요.

 

 

 

 

 

 

=match(G3,B3:B6,0)

ㄴ match(검색할 키, 검색할 범위, 일치검색을 의미하는 0)

ㄴ 해석 : G3를  범위 B3:B6에 몇 번째 행에 있는지 정확히 일치하는 단어로 찾아

 

 

 

 

 

 

4번째 행에 있으니까 4를 반환해줬네요

 

 

 

 

2. index 함수는,

범위에서 행과 열의 위치를 지정해주면 그 위치에 있는 값을 반환해 주는 함수에요.

 

 

 

 

 

 

 

=index(A3:E6,2,1)

ㄴ index(범위지정, 행, 열)

ㄴ 해석 : A3:E6 범위에서 2번째 행 1번째열에 있는 값을 반환해 줘

 

 

 

 

 

 

 

2행 1열 교차지점 kbmsSet 를 반환해 줬네요.

 

 

 

 

 

 

 

 

 

그럼 이제 중첩해서 써볼 차례네요. index 함수와 match 함수를 중첩하면 어떤 일이?

 

=index(A3:E6,match(G3,B3:B6,0),1) 

 

 

 

 

 

 

찬찬히 해석해봅시다

아까 위에서 봤다시피 match(G3,B3:B6,0) 이 함수는 무엇을 반환할까요?

match는 검색하는 키(G3)가 범위내에(B3:B6) 몇번째 행에 있는지를 숫자로 반환해주는 함수니까 

결국 match(G3,B3:B6,0) = 4  를 반환하죠? 

 

그걸 아래 함수에 대입해보면

 

index(A3:E6,match(G3,B3:B6,0),1) 

-> index(A3:E6,4,1)   이렇게 볼 수 있겠죠? 

 

자 그럼 index는 범위(A3:E6)에서 행(4)과 열(1)의 위치를 지정해주면 그 위치에 있는 값을 반환해 주는 함수니까 

결국 index(A3:E6,4,1) =Ts-penSet를 반환하겠네요!

 

 

 

그림과 색깔로 다시한번 볼까요?

 

 

 

 

 

 

H3셀에 =index(A3:E6,match(G3,B3:B6,0),1)  를 입력하면 결과는 Ts-penSet

 

 

 

 

 

이렇게 vlookup 의 한계를 index match 중첩을 이용해서 해결하게 되었습니다. 

짝짝짝!!!!!!!

 

설명하는데 당이 떨어지네요......... 

 

여러번 보고, 직접 해보시면서 본인것으로 만들어보세요~!  쓸 일이 아주 많을거에요. 

모를 때는 노가다할 수 밖에 없는데 말이에요....

 

함수를 많이 알면 알수록 응용해서 마치 코딩하듯이 쓰게 되더라구요.

그럼 알아서 다 자동으로 값이 채워지게 할 수 있어요.  

이렇게 칼퇴하는것이죠 '-'