엑셀 작업 시 다른 표의 값을 불러와서(참조해서) 사용해야 할 경우가 많습니다.
이 때, 특정 조건에 맞는 값을 참조해야 한다면 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
'달빛엑셀 > 실무엑셀' 카테고리의 다른 글
[달빛엑셀4] LARGE, SMALL 함수 - 트렌치코트 리뷰 TOP10 찾아내기 (0) | 2020.03.17 |
---|---|
[달빛엑셀 3] HLOOKUP 함수로 다른 표의 값 참조하기 (0) | 2020.03.12 |
[달빛엑셀1] vlookup 함수로 다른 표의 값 참조하기 (0) | 2020.03.10 |
댓글