엑셀실무 VLOOKUP INDEX MATCH 사용법

오늘은 엑셀실무로 알아두면 좋은 함수 중 하나인 VLOOKUP 함수 사용법에 대해 알아보겠습니다. 또한 VLOOKUP함수처럼 사용할 수 있는 MATCH함수와 INDEX함수까지 같이 살펴볼게요!

 

① VLOOKUP 함수 실무에 왜 자주 쓰이는가?

VLOOKUP 함수 사용법에 들어가기 앞서 왜 이 함수가 실무에서 자주 쓰이는지 궁금하신 분들이 있을거 같습니다.

VLOOKUP함수는 방대한 데이터 자료에서 필요한 값을 뽑아내는 함수입니다.

예를 들어 단가표를 정리한 엑셀 파일에서 특정 제품의 단가를 알고 싶을 때, 직원의 직급, 월급 등 직원 정보가 담긴 파일에서 특정 직원의 정보를 알고 싶을 때 VLOOKUP 함수를 사용하면 쉽게 찾을 수 있답니다.

 

그렇다면 또 하나의 궁금증, HLOOKUP 함수는 실무에 잘 안 쓰이는가?

참고로 VLOOKUP의 V는 vertical 로 수직방향 즉 열 기준으로 값을 찾아내는 반면 HLOOKUP의 H는 horizonal (수평방향) 즉 행 기준으로 값을 찾아냅니다.

 

보통 우리가 엑셀에 데이터를 입력할 때 ①번 방식으로 쓰는 경우가 많기 때문에 VLOOKUP 함수를 더 많이 쓰는 거랍니다. 만약 데이터를 ②번 방식으로 쓴다면 VLOOKUP보다는 HLOOKUP을 더 많이 쓰겠죠?

 

 

② VLOOKUP함수 사용법 어렵지 않아요!

	A	B	C	D
1	이름	나이	성별	주거지
2	김씨	20	여	서울
3	이씨	25	여	경기
4	박씨	27	남	부산

VLOOKUP함수는 =VLOOKUP(찾을값,데이터 포함 전체범위,열번호,0 or1) 로 입력합니다.

 

· 순수하게 VLOOKUP함수만 사용한다면 찾을값은 항상 맨 왼쪽에 위치해야 합니다. 그렇지 않을 경우엔 INDEX, MATCH 함수를 사용해야 합니다. 위 표에서 순수하게 VLOOKUP함수만 사용할 시, 찾을값은 나이, 성별, 주거지가 될 수 없고 맨 왼쪽에 있는 이름만 가능합니다. 즉 김씨, 이씨, 박씨 기준으로 나이, 성별, 주거지 정보만 추출해 낼 수 있죠!

 

· 데이터 포함 전체범위는 움직이면 안 되기 때문에 절대참조(자물쇠 걸어주기)로 설정해줘야 합니다. 위 표의 전체범위는 A1:D4이고 이 범위는 고정돼야 하기 때문에 $A$1:$D$4로 적어줘야 합니다. 보통 범위 설정 후 F4를 누르면 자동으로 절대참조로 바뀝니다. 참고로 데이터 범위 설정은 Ctrl + Shift + 화살표 (→) +화살표(↓)로 마우스 없이 키보드로 간편하게 설정할 수 있습니다.

 

· 열번호는 내가 가지고 오고 싶은 데이터의 열번호를 적어주면 됩니다. 김씨의 나이를 알고 싶다면 B열 즉 2번열이니 2를 적어주면 됩니다. 김씨의 주거지를 알고 싶다면 D열 즉 4번열이니 4를 적어주면 됩니다.

 

· 마지막 0 or 1의 경우 0은 정확히 일치하는값, 1은 유사값을 말하는데요. 보통 정확히 일치하는 값을 쓰기 때문에 0을 쓴다라고 외워 두셔도 됩니다.

 

[정리]

김씨의 나이를 알고 싶을 경우 =VLOOKUP(A2,$A$1"$D$4),2,0)

이씨의 주거지를 알고 싶을 경우 =VLOOKUP(A3,$A$1"$D$4),4,0)

박씨의 성별을 알고 싶을 경우 =VLOOKUP(A4,$A$1"$D$4),3,0)

 

 

③ VLOOKUP과 짝꿍처럼 다니는 INDEX, MATCH 함수 너흰 뭐니?

	A	B	C	D
1	이름	나이	성별	주거지
2	김씨	20	여	서울
3	이씨	25	여	경기
4	박씨	27	남	부산

INDEX함수는 =INDEX(지정범위,행번호,열번호)로 몇 행 몇 열에 위치한 값이 무엇인지 알려주는 함수입니다.

위 표를 예를 들어 =INDEX(A1:D4,2,2)라고 적으면 A1:D4라는 범위에서 2행 2열에 있는 값 '20'이 추출됩니다.

=INDEX(A1:D4,4,4)라고 적으면 A1:D4라는 범위에서 4행 4열에 있는 값 '부산'이 추출됩니다.

 

MATCH함수는 =MATCH(찾을값, 지정범위, 0 or 1 or -1)로 내가 찾을 값이 몇 행 또는 몇 열에 위치해 있는지 알려주는 함수입니다. (위치 알려주는 함수라고 생각)

위 표를 예를 들어 서울이 몇 행에 있는지 알아보고 싶다면 =MATCH("서울",D2:D4,0)

→ 지정범위를 세로방향 즉 열방향으로 잡으면 추출되는 값은 행번호입니다. 보시면 범위를 세로방향인 D2:D4로 잡았죠?  D2:D4 범위 기준으로 서울은 1행에 있기 때문에 결과값은 1이 나올 겁니다.

 

위 표를 예를 들어 서울이 몇 열에 있는지 알아보고 싶다면 =MATCH("서울",A2:D2,0)

→ 지정범위를 가로방향 즉 행방향으로 잡으면 추출되는 값은 열번호입니다. 보시면 범위를 가로방향인 A2:D2로 잡았죠?  A2:D2 범위 기준으로 서울은 4열에 있기 때문에 결과값은 4가 나올 겁니다.

 

참고로 MATCH함수 지정범위 마지막 값 0 or 1 or -1은 

0 = 정확한 값

1 = 정확히 일치하는 값은 아니고 그 값의 이상값 중 가까운 위치 값
-1= 정확히 일치하는 값은 아니고 그 값의 이하값 중 가까운 위치 값

으로 VLOOKUP처럼 보통 정확한 값을 추출하는 경우가 대부분이라 마지막은 0을 쓴다라고 외워도 괜찮습니다.

 

 

④ 찾을 값이 맨 왼쪽에 있지 않을 때? INDEX, MATCH 함수를 사용하자!

앞서 VLOOKUP함수를 사용하려면 찾을값이 맨 왼쪽에 위치해야 한다고 했습니다. 맨 왼쪽에 위치하지 않고 중간, 또는 끝에 위치할 경우엔 INDEX와 MATCH 함수를 이용해 주면 됩니다.

 

	A	B	C	D
1	나이	성별	주거자	이름
2	20	여	서울	김씨
3	25	여	경기	이씨
4	27	남	부산	박씨

만약 김씨의 성별을 알고 싶을 경우 먼저 김씨가 어디에 위치해 있는지부터 찾아줍시다. 위치 찾기는 MATCH였죠?

=MATCH(찾을값, 지정범위, 0 or 1 or -1) 이므로 =MATCH("김씨",D2:D4),0) 으로 적어줍니다.

찾을값은 김씨이며 지정범위는 세로뱡향 즉 열방향으로 했으니 값은 행번호가 나오겠구나 추측할 수 있습니다. 마지막 0은 정확한 값을 찾는 거니 0이라고 적어줬습니다. 그러면 최종값은 1이 나올 겁니다. 아! 김씨가 1행에 있구나!

 

그럼 INDEX함수로 김씨의 성별이 무엇인지 추출해 줍시다.

=INDEX(지정범위,행번호,열번호) 이므로 =INDEX(B2:B4,MATCH("김씨",D2:D4),0),1) 로 적어줍니다.

우리는 김씨의 성별을 찾는 것이니 지정범위는 성별 즉 B2:B4로 설정했습니다.

그 다음은 행번호를 입력해야 하는데 우리가 앞서 MATCH함수를 통해 김씨가 1행에 있는 걸 알아냈죠? 그러니 행번호에 MATCH함수를 복붙 해줍니다.

그 다음은 열번호입니다. 지정범위에서 열의 개수는 1개죠? 1열만 선택했으니까요. 그래서 1을 적어줬습니다.

 

이런 식으로 INDEX와 MATCH함수를 통해 VLOOKUP처럼 데이터에서 내가 원하는 값을 추출해 낼 수 있습니다.

 

 

---

오늘은 방대한 데이터 자료에서 필요한 값만 추출할 수 있는 VLOOKUP 함수 사용법뿐만 아니라 VLOOKUP함수를 사용할 수 없을 때(찾을 값이 맨 왼쪽에 위치하지 않은 경우) 사용하는 INDEX와 MATCH함수 사용법도 알아봤습니다. 참고로 이 함수들은 컴활 시험에 단골처럼 나오는 문제이기 때문에 더 살펴보고 싶다면 하단 참고하면 좋은 글을 참고해 보시면 좋을거 같습니다. 그럼 다음 포스팅도 유익한 정보로 찾아뵙겠습니다. 끝까지 읽어주셔서 감사드립니다.

 

 

 

[ 참고하면 좋은 글 ]

직장인 엑셀 INDEX MATCH 함수 사용법

 

 

VLOOKUP함수 중복값 찾기 COUNTIF 기억하자!

 

 

VLOOKUP함수 다중조건 찾을값 여러 개일 때 응용법