[엑셀] VLOOKUP 사용법 #1 함수 개념, 일치옵션, IF와 조합

VLOOKUP은 참조범위에서 기준값에 대한 데이터를 추출해주는 함수로, 회사에서 가장 많이 쓰이는 함수 중 하나입니다. 

 

이번 글에서는 VLOOKUP 함수와 관련해서 다음과 같이 정리해 보겠습니다.

① VLOOKUP 함수 개념

② 일치옵션에 따른 VLOOKUP 사용법과 용도

③IF와 VLOOKUP 조합해서 활용하기: IF(기준값=VLOOKUP(~,~,1,TRUE), VLOOKUP(~,~,출력 열번호,TRUE), ) 

*저는 회사에서 엑셀2016을 사용하기 때문에 XLOOKUP은 따로 정리하지 않았습니다.

 

 VLOOKUP 함수 개념

VLOOKUP(기준값, 데이터 범위, 추출할 열의 번호, 일치옵션)

- 기준값: 데이터범위의 첫 번째 열에서 찾고자 하는 값
- 데이터 범위: 데이터를 검색하고 추출하려는 범위
- 추출할 열의 번호: 추출하고자 열의 데이터 범위 기준 위치(열 번호)
- 일치옵션: 유사일치 경우 TRUE(=1) / 정확하게 일치한 값 추출할 경우 FALSE(=0)    

▲샘플 표를 예로 들자면, 만약에 김씨의 나이를 추출하고자

1) 기준값에 "김씨" 혹은 "김씨"가 입력된 셀을 입력하고,
2) '이름'열부터 데이터범위를 설정하고(기준값은 데이터범위의 첫번째 열에 있어야 함),

3) 데이터범위 기준으로 '나이'는 두번째 열이므로, 추출할 열의 번호에 2를 기입하고,

4) 일치옵션은 정확하게 일치한 값을 추출하도록 설정한 것입니다(FALSE 혹은 0 기입). 

 

일치옵션에 따른 VLOOKUP 사용법과 용도

1) 정확하게 일치한 값 추출 옵션 : VLOOKUP(기준값, 데이터 범위, 추출할 열의 번호, FALSE 혹은 0)

 

말 그대로 기준값과 정확하게 일치한 값의 데이터를 추출하고자 할 때 활용합니다. 

일치옵션을 유사일치로 할 경우 조건에 따라 오류가 발생할 수 있음

▲일치옵션을 유사일치(TRUE 혹은 1)로 설정할 경우 기준값의 성질(문자/숫자/혼합 여부, 길이 등), 데이터범위 크기, 기준열의 정렬방식(오름차순 등) 등의 요인에 따라 데이터를 추출하지 못하거나, 정말 기준값과 유사한 값의 데이터를 출력하는 등의 오류가 발생할 수 있습니다. 만약에 10만행이 넘는 로데이터 간에 VLOOKUP 함수를 활용하는 경우가 종종 있는데, 이런 경우 유사일치로 설정하면 오류가 쉽게 발생합니다. 

 

다만, 정확하게 일치한 값을 추출하는 옵션은 데이터범위가 굉장히 클 경우 계산이 오래 걸릴 수 있습니다.

 

2) 유사일치 추출 옵션: VLOOKUP(기준값, 데이터 범위, 추출할 열의 번호, TRUE 혹은 1)

 

연령대, 점수 등급 등 구간을 설정할 때 굉장히 유용합니다.

연령대 등과 같이 구간을 정할 때 유용함

위의 사진은 샘플 표의 연령대를 '연령대 구분'이라는 별도의 데이터범위에서 추출한 것입니다.

0살 부터 바로 아래의 셀 값 미만까지는 0살의 연령대 데이터를 추출하는 방식으로 작동합니다. 그래서 '연령대' 데이터범위에서 나이를 오름차순으로 정렬하지 않으면, 연령대 그룹을 잘못 추출할 수 있습니다.

다시 말해, 구간을 나눌 때는 데이터범위의 첫번째 열을 오름차순으로 정렬해야 합니다.  

 

IF와 VLOOKUP 조합해서 활용하기

수식: IF(기준값=VLOOKUP(~,~,1,TRUE), VLOOKUP(~,~,출력 열번호,TRUE), )

 

IF 구문과 VLOOKUP 구문을 조합하여, 유사일치 옵션으로도 정확한 값을 빠르게* 추출하는 방법입니다. 

*VLOOKUP함수 계산 속도는 일치옵션을 유사일치로 할 때가 정확한 값을 찾도록 설정할 때보다 빠릅니다.

IF(기준값=VLOOKUP(기준값,데이터범위,1,TRUE), VLOOKUP(기준값,데이터범위,출력 열번호,TRUE), )
단, 데이터범위 첫번째 열 오름차순 정렬 및 머릿글 제외

<설명>
- IF함수의 조건부분: 기준값=VLOOKUP(기준값,데이터범위,1,TRUE)
→ 기준값과 VLOOKUP 유사일치 옵션으로 데이터범위 첫번째 열(=기준값 열)에서 출력한 값이 일치하다면
- IF함수의 조건 TRUE 시 출력값: VLOOKUP(기준값,데이터범위,출력할 열번호,TRUE)
→ VLOOKUP 함수 유사일치 옵션
- IF함수의 조건 FALSE 시 출력값 : 공백

VLOOKUP(기준값,데이터범위,1,TRUE)은 오류가 발생하지 않는다면, 기준값을 추출합니다. 즉, IF함수의 조건부분인 '기준값=VLOOKUP(기준값,데이터범위,1,TRUE)' 에서 잘못된 데이터를 추출하는 오류를 1차적으로 방지하는 것입니다. 

 

그리고 '기준값=VLOOKUP(기준값,데이터범위,1,TRUE)'이 참이라면, 유사일치를 사용하더라도 데이터를 잘못 추출할 경우가 아니므로 VLOOKUP(기준값,데이터범위,출력할 열번호,TRUE)을 출력하는 것입니다. 

 

IF구문과 VLOOKUP 유사일치 옵션을 조합하면, 데이터범위가 클수록 계산 속도가 정확히 일치하는 옵션을 활용했을 때보다 빠르다는 것을 더 체감할 수 있습니다. 복수의 로데이터를 결합할 때 활용하면 좋습니다. 

 

 

▲단, IF구문과 VLOOKUP 유사일치 옵션을 조합하기 전에 위의 사진처럼 데이터범위의 첫번째 열을 오름차순으로 정렬하고, 데이터범위를 설정할 때 머릿글(열 이름 부분)은 제외해야* 오류가 발생하지 않습니다. 

*이는 사실 VLOOKUP(기준값, 데이터 범위, 추출할 열의 번호, TRUE 혹은 1)을 사용했을 때 오류를 줄이는 방법과 동일합니다. 

---

 

다음 글에서는 이번글에 이어서 VLOOKUP 사용법을 다음과 같이 소개하겠습니다. 

④ VLOOKUP과 MATCH 함수 조합해서 활용하기

⑤ INDEX 함수와 MATCH 함수 조합해서 활용하기

⑥ VLOOKUP 함수로 다중조건 검색

 

<관련 글>

[엑셀 함수] COUNT 함수의 종류 #1 COUNT, COUNTA, COUNTBLANK

[엑셀 함수] HLOOKUP 함수 사용 방법

[파워쿼리] 파워쿼리로 다중조건 VLOOKUP 손쉽게 하기

[파워쿼리- 쿼리 생성하는 5가지 방법] #5 빈 쿼리로 쿼리 생성하기

VLOOKUP 함수의 여러 사용법과 용도 #2

[엑셀 기능] 표(테이블)과 피벗테이블