구글 스프레드 시트 9 - 원하는 값 찾기 함수(VLOOKUP, HLOOKUP, INDEX, MATCH)

오늘은 서로 다른 두시트에서 원하는 값을 찾아서 가져오는 함수에 대해서 알아보도록 하겠습니다.

 

원하는 값을 찾아오는 함수 중 자주쓰이는 함수

함수	기능	정의
VLOOKUP	열 방향 검색, 범위의 첫 번째 열에서 키를 검색한 다음 키가 있는 행에서 지정된 셀의 값을 반환	support.google.com/docs/answer/3093318?hl=ko
HLOOKUP	행 방향 검색, 범위의 첫 번째 행에서 키를 검색한 다음 키가 있는 열에서 지정된 셀의 값을 반환	support.google.com/docs/answer/3093375?hl=ko
INDEX	행과 열 오프셋으로 지정된 셀 내용을 반환	support.google.com/docs/answer/3098242?hl=ko
MATCH	범위에서 지정된 값과 일치하는 항목의 상대적 위치를 반환	support.google.com/docs/answer/3093378

 

1. VLOOKUP

VLOOKUP은 세로 포멧으로 되어있는 데이터 시트끼리 활용이 되는 함수이며 우리가 1조건내에서 검색할 때 많이 활용하는 함수 입니다. 

 

 

예를들어 아래의 전화번호부와 부서명부가 있다고 가정해봅시다.

스프레드 시트 VLOOKUP 예제

부서명부에 전화번호를 붙이고 싶으면 어떻게 해야 할까요?

그냥 일일이 찾아서 복사하는 방법도 있겠지만
만약 명부에 엄청 많은 인원이 있다면 일일이 손으로 하는게 불가능 하겠죠.

 

이 예시에서 두 시트를 매칭 할때 이름 하나를 기준으로 매칭이 가능하겠죠?

 

이때 활용하는게 VLOOKUP입니다.

 

=VLOOKUP(검색할단어, 검색범위, 반환열, 정렬여부)
검색할단어: 검색의 대상이 되는 단어 

검색범위: 총 검색할 시트의 범위

반환열: 시트중 가져올 열 번호

정렬여부:  [기본적으로 TRUE] - 검색 열(지정된 범위의 첫 번째 열)의 정렬 여부를 나타냄

              대부분의 경우 FALSE로 설정하는 것이 좋음

 

예시로 들어가보죠

해당 예시를 봤을땐 D3에 있는 김철수라는 값을 키로 전화번호부인 A3:B10까지로 검색하여 정렬여부는 FALSE로 2번열인 전화번호를 가져와라 입니다.

스프레드 시트 VLOOKUP 예시

=vlookup(D3,$A$3:$B$10,2,FALSE)

D3: 김철수, 검색할 키
$A$3:$B$10: 전화번호 시트
2: 반환한 열의 번호
FALSE: 정렬여부

 

이런식으로 1조건에서 세로포멧의 시트를 가지고 값을 찾을 때는 VLOOKUP을 활용하면 쉽게 매칭할 수 있습니다.

 

 

2. HLOOKUP

HLOOKUP은 가로 포멧으로 되어있는 데이터 시트끼리 활용이 되는 함수이며 우리가 1조건내에서 검색할 때 많이 활용하는 함수 입니다. 

 

예를들어 아래의 전화번호부와 부서명부가 있다고 가정해봅시다.

스프레드시트 HLOOKUP 예제

 

위의 VLOOKUP처럼 부서명부에 전화번호를 값을 찾아서 붙이고 싶으면 어떻께 해야할까요?

VLOOKUP과 동일하게 이름하나를 기준으로 가로포멧 데이터를 가져와야 하니 HLOOKUP이 적당한 방법이 될 것 입니다.

 

=HLOOKUP(검색할단어, 검색범위, 반환행, 정렬여부)
검색할단어: 검색의 대상이 되는 단어 

검색범위: 총 검색할 시트의 범위

반환행: 시트중 가져올 행 번호

정렬여부:  [기본적으로 TRUE] - 검색 열(지정된 범위의 첫 번째 열)의 정렬 여부를 나타냄

              대부분의 경우 FALSE로 설정하는 것이 좋음

 

예시로 들어가보죠

해당 예시를 봤을땐 A20에 있는 김철수라는 값을 키로 전화번호부인 N16:I17까지로 검색하여 정렬여부는 FALSE로 2번행인 전화번호를 가져와라 입니다.

 

스프레드 시트 HLOOKUP 예시

=hlookup(A20,$B$16:$I$17,2,FALSE)

A20: 김철수, 검색할 키
$B$16:$I$17: 전화번호 시트
2: 반환한 행의 번호
FALSE: 정렬여부

 

이런식으로 1조건에서 가로포멧의 시트를 가지고 값을 찾을 때는 HLOOKUP을 활용하면 쉽게 매칭할 수 있습니다.

 

 

3. INDEX와 MATCH

INDEX와 MATCH 함수 두개를 같이 활용하면 원하는 값을 찾아올 수 있습니다.

 

먼저 함수 설명부터 해보겠습니다.

 

=INDEX(검색범위, 행, 열, 범위)

 

검색범위 내에서 원하는 행과 열 번호를 가진 셀을 반환

스프레드 시트 INDEX 함수 설명

검색범위: 어디까지 검색할 것인가

행: 검색범위에서 몇번 행을 가져올 것인가

열: 검색범위에서 몇번 열을 가져올 것인가

 

=MATCH(검색할_키, 검색_범위, 검색_유형)

 

검색 범위에서 해당 검색어가 있는 행 위치를 반환

 

스프레드 시트 MATCH 함수 설명

검색할_키: 어떤 단어로 검색할 것인가
검색_범위: 어디까지 검색할 것인가

검색_유형(선택사항): 기본값은 1

- 1이 기본값으로, 이 경우 MATCH는 범위가 오름차순으로 정렬된 것

- 0은 완전 일치

- -1일 경우 MATCH는 범위가 내림차순으로 정렬된 것

 

 

정리하자면 INDEX는 위치기반의 값을 가져오는 함수이고 MATCH 함수는 상대적인 위치를 가져오는 함수이니 INDEX함수안에 MATCH 함수를 넣으면 값을 찾아올 수 있습니다.

 

 

위의 동일한 VLOOKUP 세로포멧의 두시트가 있을때의 예시를 보겠습니다.

 

스프레드 시트 INDEX와 MATCH 함수 혼용 예시

 

위의 예시함수는 이렇습니다.
=index($B$3:$B$10,match(D3,$A$3:$A$10,0))

해부해서 보게 되면

 

=index($B$3:$B$10

1. INDEX 함수에 B열(전화번호)만 넣음으로써 행 번호만 받아도 값이 나오게 범위를 정했습니다.

 

match(D3,$A$3:$A$10,0)

2. MATCH 함수내에서 D3(김철수)라는 검색키로 A열(이름)에 있는 김철수의 행 위치를 정확히 매칭 시킨 것입니다.

 

=INDEX(전화번호, 이름이 매칭된 위치)

그래서 결론적으로 전화번호 열 내에서 이름이 매칭된 위치에 대한 전화번호 값을 가져오게 되는 것입니다.

 

 

마치며

오늘은 1조건 내에서 값을 찾아서 매칭하는 함수에 대해서 알아봤습니다. 다음시간에는 조금 꼼수(?)를 활용해서 두조건 이상의 매칭일 필요할때는 어떻게 해야하는가에 대해서 알아보도록 하겠습니다.