VLOOKUP 함수의 여러 사용법과 용도 #1에 이어 VLOOKUP 함수와 연관된 사용법들을 소개해 보겠습니다.
④ VLOOKUP과 MATCH 함수 조합해서 활용하기
⑤ INDEX 함수와 MATCH 함수 조합해서 활용하기
⑥ VLOOKUP 함수로 다중조건 검색
VLOOKUP과 MATCH 함수 조합해서 활용하기
VLOOKUP(기준값, 데이터범위, MATCH(추출할 열의 머릿글, 데이터범위 머릿글, 0), 일치옵션)
- MATCH 함수: MATCH(찾을 값, 데이터범위(줄 형태), 일치옵션)
- VLOOKUP의 '추출할 열의 번호' 부분: MATCH(추출할 열의 머릿글, 데이터범위 머릿글, 0)
→ MATCH 함수는 찾을 값(추출할 열의 머릿글)의 데이터범위(데이터 범위의 머릿글) 내 순번을 반환
→ 따라서, MATCH(추출할 열의 머릿글, 데이터범위 머릿글, 0)는 추출할 열의 번호를 출력함
VLOOKUP 함수는 데이터범위의 열 갯수가 많거나 추출하고자 하는 데이터가 많은 경우 사용하기가 번거롭습니다. 그럴 때 추출할 열의 번호를 MATCH 함수를 통해 더 효율적으로 구할 수 있습니다. 뿐만 아니라, 드래그 또는 함수 복사/붙여넣기를 통해 여러 기준값의 복수의 데이터를 추출하는데 유용합니다.
위의 사진처럼 김씨와 홍씨의 나이와 연령대를 추출해 봅시다. 일반적인 VLOOKUP 함수를 활용한다면, 기준값을 열고정 혼합방식으로 참조하고 나이 부분(D4)에는 열 번호를 2, 연령대 부분(E4)에는 3을 입력한 후, 아래로 드래그하거나 복사/붙여넣기를 해야됩니다. 찾고자 하는 데이터가 나이와 연령대 두 가지 밖에 없어서 그렇게 번거롭지는 않지만, 추출할 데이터가 많아질수록 자잘한 작업량이 늘어날 것입니다.
하지만 추출할 열 번호 부분에 MATCH 함수를 사용한다면, MATCH 함수가 추출할 열의 번호를 자동적으로 반환해주기 때문에 함수를 한 번만 입력해도 됩니다. 단, MATCH함수에서 추출할 열의 머릿글은 데이터 방향에 따라 행고정 혹은 열고정 혼합참조를 해야 합니다.
D4에 입력된 VLOOKUP($G4,$C$4:$E$8,MATCH(H$3,$C$3:$E$3,0),FALSE)의 MATCH함수 부분은 {이름, 나이, 연령대}($C$3:$E$3) 영역 내 '나이(H$3)'의 순번(=2)을 반환합니다.
바로 위의 사진처럼 기준값(이름)은 열고정 혼합참조($G4), 추출할 데이터(나이)는 행고정 혼합참조(H$3)되어 있어, 다른 영역에 함수를 바로 복사해도 김씨의 연령대, 홍씨의 나이와 연령대 등 원하는 데이터를 한 번에 추출할 수 있습니다.
INDEX 함수와 MATCH 함수 조합해서 활용하기
INDEX(데이터범위, 행 번호, 열 번호) ▶ 데이터범위 내 x 행 y 열 값 반환
INDEX(데이터범위, MATCH(기준값, 기준열, 0), MATCH(추출할 데이터 항목, 데이터범위 머릿글, 0))
→ MATCH 함수는 위치 반환 함수: 기준값의 행 번호, 데이터 항목의 열 번호 추출
→ INDEX 함수를 통해 데이터 추출
▲VLOOKUP 함수를 사용할 때 유의할 점은 기준값이 무조건 데이터범위의 가장 왼쪽에 있어야 한다는 것입니다. 위의 샘플 표에서는 '이름' 열의 위치를 옮겨주지 않는 이상 '성별' 데이터를 추출할 수 없습니다.
▲하지만 INDEX 함수와 MATCH 함수를 활용하면 기준값 열의 왼쪽에 있는 데이터도 추출할 수 있습니다. 위의 사진은 INDEX 함수와 MATCH 함수를 통해 데이터범위(샘플표)의 2행('이름열'에서 '김씨'의 순번), 1열(표 머릿글 내 '성별'의 순번)의 값을 추출한 것입니다.
▲VLOOKUP 함수와 MATCH 함수의 조합과 마찬가지로 참조방식을 알맞게 설정해주면, 한 번의 복사 붙여넣기로 다양한 기준값의 데이터 항목을 추출할 수 있습니다.
VLOOKUP 함수로 다중조건 검색
▲원하는 데이터 항목들을 '&' 합친 열을 만들면, 간단히 VLOOKUP에 다중조건을 걸 수 있습니다. 위의 표에서는 모델과 세부기종을 &으로 묶는 열을 생성했습니다.
▲위의 사진처럼 VLOOKUP 구문을 작성할 때 데이터 항목들을 &으로 묶어서 기준값으로 설정하여 조회할 수 있습니다. 배열수식 사용 등 다른 방법도 있지만, 일종의 tag 열을 만드는 방법이 가장 직관적이고 때문에 열을 추가하는 방법을 더 추천드립니다.
행과 열의 갯수가 많아지기 시작하면, IF문과 VLOOKUP문을 혼합하여 사용해도 계산이 오래걸리거나 구문을 작성하기 번거로워 질 것입니다. 추후 다른 글에서 소개하겠지만, 행이 많은 여러 데이터를 결합하는 경우 VLOOKUP 함수를 사용하는 것보다 파워쿼리의 '쿼리 결합' 기능을 활용하는 것이 훨씬 간편하고 효율적입니다.
참고: 파워쿼리에 대한 간단한 소개글
[엑셀 기능] 엑셀 주요기능 세 가지: 스프레드시트, 매크로/VBA, 파워쿼리
엑셀 2016 기준으로, 현재 내가 활용 중인 엑셀 기능은 ① 일반 스프레드시트, ② 매크로/VBA, ③ 파워쿼리, 이렇게 크게 세가지*로 나눌 수 있다. *파워피벗은 엑셀 2016 기준으로 제공되지 않으며,
writingpjt.tistory.com
관련 글
[엑셀 함수] COUNT 함수의 종류 #1 COUNT, COUNTA, COUNTBLANK
[회사에서 많이 쓰이는 엑셀 함수] IF 함수 소개 및 사용법 #1
[파워쿼리] 파워쿼리로 다중조건 VLOOKUP 손쉽게 하기
[파워쿼리] 한 개의 파일 내 여러 데이터 합치기 (VLOOKUP 상위호환)
'엑셀, VBA, 파워쿼리 > 엑셀 스프레드시트' 카테고리의 다른 글
| [엑셀] LEN 함수, 셀의 길이를 알려주는 함수 (0) | 2023.10.12 |
|---|---|
| [엑셀] 표(테이블)과 피벗테이블 (0) | 2023.10.06 |
| [엑셀] VLOOKUP 사용법 #1 함수 개념, 일치옵션, IF와 조합 (1) | 2023.10.04 |
| [엑셀] 정렬 기능으로 데이터 열 순서 바꾸는 방법 (1) | 2023.10.04 |
| [엑셀 함수] COUNT 함수의 종류 #2 COUNTIF, COUNTIFS (0) | 2023.01.03 |
댓글