[달빛엑셀 2] INDEX, MATCH 함수로 다른 표의 값 참조하기

 

엑셀 작업 시 다른 표의 값을 불러와서(참조해서) 사용해야 할 경우가 많습니다.

이 때, 특정 조건에 맞는 값을 참조해야 한다면 vlookup과 같은 참조 함수를 사용하는 것이 좋습니다.

 

​그렇지만vlookup 함수는 참조하고자 하는 표의 왼쪽 첫번째 열에서 필요한 값을 찾아 오른쪽 열에 위치한 값을 참조할 때 사용하는 함수입니다. 때문에 표가 원하는 방식으로 정리되어 있지 않으면 vlookup 함수로 원하는 값을 참조할 수 없습니다.

 

이런 경우에는 index, match 함수 조합으로 수식을 작성하면 값을 참조할 수 있습니다.

INDEX 함수는 참조 작업만 수행하고, MATCH 함수는 찾기 작업만 수행하므로 보다 세밀하게 수행할 수 있다는 장점이 있습니다.

 

-------------------------------------------------

=INDEX(표,행 번호,열 번호,영역 번호)

(1) 표 : 구할 값을 포함하고 있는 데이터의 범위입니다.

(2) 행 번호 : 표에서 참조할 값이 위치한 행의 번호 입니다.

*n번째 행의 번호는 n 입니다.

(3) 열 번호 : 표에서 참조할 값이 위치한 열의 번호입니다.

*n번째 열의 번호는 n 입니다.

(4) 영역 번호 : 표 인수에 다중 범위를 지정했을 경우, 참조할 범위의 번호입니다.

*생략하면 첫번째 범위에서 값을 참조합니다.

 

=MATCH(찾을 값,찾을 범위,찾기 옵션)

(1) 찾을 값 : 찾을 범위에서 찾을 값 입니다. 조건에 해당합니다.

(2) 찾을 범위 : 찾을 값이 포함 된 단일 열 또는 단일 행 데이터 범위입니다.

(3) 찾기 옵션

: 0 = 정확히 일치

: 1 또는 생략 = 보다 작음

* 찾을 범위의 값이 오름차순으로 정렬되어 있다고 가정하고,

찾을 값보다 큰 값을 만날 때까지 찾을 값을 찾지 못하면 작은 값 중에서 가장 큰 값의 위치를 찾습니다.

: -1 = 보다 큼

* 찾을 범위의 값이 내림차순으로 정렬되어 있다고 가정하고,

찾을 값보다 작은 값을 만날 떄까지 찾을 값을 찾지 못하면 큰 값 중에서 가장 작은 값의 위치를 찾습니다.

-------------------------------------------------

 

예제를 활용해 보겠습니다.

 

예제[표. 직원명부 및 급여]에 [표. 직위별 보너스 비율]을 참조하여 직원별 보너스비율을 산출하겠습니다.

 

 

예제1. VLOOKUP 함수를 활용합니다.

I열에 참조하여 출력해 보겠습니다.

 

=VLOOKUP(찾을 값,표,열 번호,찾기 옵션)

(1) 찾을 값 : [표. 직원명부 및 급여]의 직위 = E5

(2) 표 : 찾을 데이터의 범위 = [표. 직위별 보너스 비율] = I17~J20

(3) 열 번호 : [표. 직위별 보너스 비율] 중 '보너스 비율' 열 = 표 중 2번 열

(4) 찾기 옵션 : FALSE = 정확히 일치

 

=VLOOKUP($E5,$I$17:$J$20,2,FALSE)

 

예제2. INDEX 함수와 MATCH 함수를 활용합니다.

J열에 참조하여 출력해 보겠습니다.

 

[참조] INDEX(표,행번호,열번호,영역번호) = 보너스비율을 불러옵니다.

(1) 표 = 구할 값을 포함하고 있는데이터의 범위

* 구할 값은 보너스 비율 = 보너스 비율의 데이터는 J17~J20에 위치 함

(2) 행 번호 = 표에서 참조할 값이 위치한 행의 번호

* 참조할 값은 직위 = 직위는 현재로서는 어느 행에 있는 지알 수 없음∴MATCH 함수로 찾음

(3) 열 번호 = 표에서 참조할 값이 위치한 열의 번호 = 1

(4) 영역 번호 = 표 인수에 다중 범위를 지정했을 경우, 참조할 범위의 번호

* 다중 범위가 아니므로 영역 번호는생략 함

 

[찾기] =MATCH(찾을값,찾을범위,찾을옵션) = 직위를 찾습니다.

(1) 찾을 값 = 찾을 범위에서 찾을 값 = 조건 값

* 찾을 값은 직위 = 직원 명부 및 급여 표의 직위가 찾을 값 = E5~E12

(2) 찾을 범위 = 찾을 값이 포함 된 단일 열 또는 단일 행 데이터 범위

* 찾을 범위는 불러올(참조할) 표의 직위가 있는 범위 = I17~I20 입니다.

(3) 찾기 옵션 = 정확히 일치 = 0

 

=INDEX(J17~20을 참조하여 불러옵니다,직위 E5~E8을 MATCH함수로 직위 I17~20 행에서 찾고, 1열에서, 정확히 일치하도록)

=INDEX($J$17:$J$20,MATCH($E$5:$E$12,$I$17:$I$20,0),1,1)

 

예제풀이

대표사진 삭제

사진 설명을 입력하세요.

 

 

요약

VLOOKUP 함수를 INDEX, MATCH 함수로 전환하면 아래와 같습니다.

 

=VLOOKUP(찾을 값,표,열 번호,찾기 옵션)

=INDEX(표,MATCH(찾을 값,찾을 범위,찾기 옵션),열 번호,영역 번호)

 

궁금한 내용은 댓글로 남겨 주세요~ 감사합니다!

 

TIP & NEXT

업무 상 사용해 보면 INDEX와 MATCH함수도 활용도가 높지만

저는 참조 함수 중에서 VLOOKUP이 가장 편리했습니다.

표 여러개에 흩어져 있는 데이터를 참조하기에는 최고에요.

 

그렇지만 오른쪽 열에서 찾아 왼쪽 열을 구해야 해서 VLOOKUP을 사용할 수 없다면,

간단하게 참조할 표를 수정하는 방법도 있습니다.

 

다음 번에는 간단하게 참조할 표를 수정하는 방법을 설명해 보겠습니다.

 

#엑셀 #함수 #excel배우기 #excel #index #vlookup #match