이번에는 VLOOKUP 함수와 유사한 기능을 함수를 알아보겠습니다. 바로 엑셀의 INDEX() MATCH()를 조합하는 방법이죠. 함수 각각의 기능을 살펴보고 조합하는 방법 순으로 알아보겠습니다.
지난 포스팅에서 vlookup 함수를 사용하는 방법을 알아보았습니다.
2021.07.14 - [보고서 작성기술/엑셀] - 엑셀 VLOOKUP 함수로 원하는 데이터 가져오기, 등급 부여하기
INDEX() 함수 이해하기
엑셀에서 INDEX()의 설명은 "표나 범위 내에서 값이나 참조 영역을 구합니다"라고 설명하고 있습니다.
쉽게 말하면 범위를 정하고, 범위 안에의 행 번호, 열 번호 위치에 있는 값을 가져오는 함수입니다.
= INDEX(범위, 행번호, 열번호) 라고 기입합니다.
예를 들어, 아래 표(범위)에서 3번째 행, 2번째 열의 값을 가져온다고 해봅시다.
[함수 삽입]에서 INDEX함수를 선택하고, "=INDEX(A1:D11,3,2)"로 기입하면
아래와 같이 지정한 범위에서 3번째 행과 2번째 열에 있는 데이터 "관우"를 가져오게 됩니다.
의외로 쉽죠?
MATCH() 함수 이해하기
"배열에서 지정된 순서상의 지정된 값에 일치하는 항목의 상대 위치 값을 찾습니다."라고 하고 있습니다.
엑셀에서 MATCH 함수의 설명을 쉽게 말하면 "찾을 값을 기입하면 범위 안에서 몇 번째 행 또는 열에 있는지 숫자를 나타내는 함수"입니다.
= MATCH(찾을 값, 범위, 찾는 옵션)라고 기입합니다.
이 중에서 찾는 옵션이 있는데 찾는 옵션은 3가지가 있습니다.
-1 = 이상(크거나 같은) 값 중 최고 가까운 위치 값(행, 열)
0 = 정확히 일치하는 위치 값(행, 열)
1 = 이하(작거나 같은) 값 중 최고 가까운 위치 값(행, 열)
예를 들어, 아래 테이블에서 "관우"가 몇 번째 행에 있는지 가져온다고 합시다.
① 찾을 값 : 지정하는 방법은 "관우"라고 이름을 기입하던가 셀주소(L4)를 입력하면 됩니다.
② 범위 : 몇 번째 열인지를 찾는 것이기 때문에 [이름]이 있는 B열(B1:B11)을 선택합니다.
③ 옵션 : "관우"라는 이름이 정확해야 하기 때문에 "0"을 선택합니다.
이를 함수식으로 나타내는 =MATCH(L4,B1:B11,0)로 표현할 수 있고 값은 "3"을 가져오게 됩니다.
예를 들어, 아래 테이블에서 "관우"가 몇 번째 열에 있는지 가져온다고 합시다.
① 찾을 값 : 지정하는 방법은 "관우"라고 이름을 기입하던가 셀주소(L4)를 입력하면 됩니다.
② 범위 : 몇 번째 열인지를 찾는 것이기 때문에 [이름]이 있는 3행(A3:D3)을 선택합니다.
③ 옵션 : "관우"라는 이름이 정확해야 하기 때문에 "0"을 선택합니다.
이를 함수식으로 나타내는 =MATCH(L4,A3:D3,0)로 표현할 수 있고 값은 "2"을 가져오게 됩니다.
두 개 사례를 합쳐보면 "관우"는 3행, 2열에 위치함을 알 수 있습니다.
INDEX()와 MATCH() 함수 조합하기
두 개 함수를 조합하여 2가지 조건에 맞는 값을 가져와 봅시다.
예를 들어, 이름이 "관우"의 "등급"을 가져온다고 해봅시다.
먼저 INDEX() 함수를 쓰고, INDEX() 함수 안에 행과 열 입력 칸에 MATCH() 함수를 사용하면 됩니다.
복잡해 보이지만 하나씩 따라 해 봅시다.
INDEX(범위, 행 번호, 열 번호)
① 범위 : 가져올 범위를 "A1:C11"와 같이 지정합니다.
② 행 번호 : MATCH() 함수를 써서 "관우"가 몇 번째 행에 있는지 구해봅시다.
MATCH(찾을 값, 범위, 찾는 옵션)
(1) 찾을 값 : "관우" 또는 셀주소(F2) 입력
(2) 범위 : 행을 구하기 때문에 이름이 있는 행(A1:A11)를 입력
(3) 찾는 옵션 : 정확한 값이기 때문에 "0"을 입력
이렇게 입력하면 MATCH(F2,A1:A11,0) 가 되겠지요.
③ 열 번호 : MATCH() 함수를 써서 "등급"이 몇 번째 열에 있는지 구해봅시다.
MATCH(찾을 값, 범위, 찾는 옵션)
(1) 찾을 값 : "등급" 또는 셀 주소(G1) 입력
(2) 범위 : 열을 구하기 때문에 등급이 있는 행(A1:C1)를 입력
(3) 찾는 옵션 : 정확한 값이기 때문에 "0"을 입력
이렇게 입력하면 MATCH(G1,A1:C1,0) 가 되겠지요.
함수식을 완성하면 =INDEX(A1:C11,MATCH(F2,A1:A11,0),MATCH(G1,A1:C1,0)) 이 됩니다.
자 이제 동일한 함수식을 복사해서 다른 곳(G3:G5)에도 적용해야겠지요?
이럴 때는 그냥 복사하면 안 됩니다.
바뀌지 말아야 할 부분은 절대주소(F4 또는 $ 표시)로 바꾸어 줘야 합니다.
지금까지 VLOOKUP 함수와 동일한 기능을 가진 INDEX와 MATCH 함수에 대해서 알아 보았습니다.
직장인에게 도움이 엑셀 함수는 아래 포스팅을 참조해 주세요
2022.03.25 - [보고서 작성기술/엑셀] - 엑셀 반올림, 올림, 버림 함수 한 번에 알아보기
2022.04.06 - [보고서 작성기술/엑셀] - 엑셀 랜덤 함수 : 엑셀 랜덤 추출, 순위 표시, 당첨자 뽑기
2021.08.05 - [보고서 작성기술/엑셀] - 엑셀 원하는 텍스트 추출(LEFT, RIGHT, LEN, FIND 함수)
2021.07.29 - [보고서 작성기술/엑셀] - 엑셀 셀 합치기(CONCATENATE 함수, 기호&)
2021.07.22 - [보고서 작성기술/엑셀] - 엑셀 COUNTIF 함수, COUNTIFS 함수로 원하는 값 개수 구하기
2021.06.25 - [보고서 작성기술/엑셀] - 엑셀 TIP - 엑셀로 CAGR 계산하기
'보고서 작성기술 > 엑셀 실무기초' 카테고리의 다른 글
| 엑셀 TIP - 엑셀 위치 고정(원하는 행, 원하는 열, 셀고정) (5) | 2021.07.28 |
|---|---|
| 엑셀 COUNTIF 함수, COUNTIFS 함수로 원하는 값 개수 구하기 (17) | 2021.07.22 |
| 엑셀 VLOOKUP 함수로 원하는 데이터 가져오기, 등급 부여하기 (0) | 2021.07.14 |
| CAGR(연평균복합성장율) 한방에 이해하기 : CAGR 개념, 계산식, 엑셀로 CAGR 계산하기 (0) | 2021.06.25 |
| 엑셀 텍스트 나누기 (0) | 2021.06.20 |
댓글