내배내적 엑셀 - index-match 함수 기본 응용하기

 

 

 

index와 match 함수에 대해 이해했으면 본격적으로 활용해보겠습니다.

혹시 index, match 함수에 대한 이해가 필요하신 분은 아래 링크를 클릭해서

각각의 함수를 이해하고 이 장으로 넘어와주세요!

 

내배내적 엑셀 - index 와 match 함수 이해하기

내배내적 엑셀 - index 와 match 함수 이해하기

 

index-match 함수예제.xlsx

0.01MB

 

index-match 함수 사용

 

index, match 함수에 대한 설명을 보시면서 혹시 눈치채셨나요?

 

index 함수는 행과 열을 무작위로 추출할 때에는 유용하지만, 
반대로 내가 값을 찾고자 할 때에는 쓸모없는 함수라는 걸 알 수 있습니다.

(2행 1열에 뭐가 있는지는 금방 찾지만 '박지수'를 찾으려면

거꾸로 박지수가 몇 행 몇 열인지 수동으로 찾아야 하니까요.)

 

그렇기 때문에 index 함수 독립적으로 쓰이지 않는건데요,

이는 반대로 말하면 행과 열의 위치를 안다면 유용하다라고도 말할 수 있습니다.

 

그럼 특정 값의 행과 열의 위치를 찾아주는 함수가 뭐였죠? 바로 match 함수입니다.

 

이 말을 순서대로 조합해보면,

 

우리는 해당 위치의  "값"이 궁금하기 때문에 값을 반환해주는 index가 맨 앞으로 와야 합니다.

 

또한 index 함수에서의 행 또는 열의 위치를 알아야 하기 때문에

match 함수가 index 함수의 row_num 또는 column_num에 들어와야 합니다.

 

index-match 함수

■ 열에서 기준 값을 찾는 경우(vlookup과 유사) =INDEX(array,MATCH함수,열번호)

■ 행에서 기준 값을 찾는 경우(Hlookup과 유사) =INDEX(array,행번호,MATCH함수)

 

이 말의 뜻을 이해하셨다면 index-match 함수는 거의 끝난 겁니다.

 

그럼 본격적으로 소개해드릴게요.

시작하기 전에 왼쪽에서 오른쪽으로 가는 건 vlookup에서 이미 진행했으니,

이번에는 오른쪽에서 값을 찾아서 왼쪽으로 가는 걸로 진행하겠습니다.

 

MATCH 함수가 들어가는 값을 제외하고 row_num 또는 column_num는

index 함수의 array의 행/열 순서를 적으면 됩니다.

 

이건 같이 진행하면서 알려드릴게요!

 

 

 

 

 

■ 열에서 기준 값을 찾는 경우(vlookup과 유사) =INDEX(array,MATCH함수,열번호)

 

저는 아래와 같이 사하구, 송파구에 살고 있는 사람을 구하려고 합니다.

 

array의 범위는 값을 찾고자 하는 구에서 부터 이름이 포함된 B1:E12가 됩니다.

 

그 다음에 우리는 row_num와 column_num를 입력해야 하는데,

 

사하구, 송파구 등 이들의 대표값인 "구" 경우 열의 위치는 E열로 동일한데, 행 위치만 1,2,3… 으로 변화합니다.

따라서 변화하는 row_num 값에 MATCH 함수를 넣어줍니다.

(사하구의 위치는 MATCH(G2,E1:E12,0) 입니다.)

 

마지막으로 "이름"열은 B1:E12 테이블에서 1열을 차지하므로 column_num는 1을 적어줍니다.

 

 

■ 사하구 =INDEX($B$1:$E$12,MATCH(G2,$E$1:$E$12,0),1)
■ 금정구 =INDEX($B$1:$E$12,MATCH(G3,$E$1:$E$12,0),1)

 

그러면 위와 같이 사하구에 사는 '김민준'이란 값이 도출됩니다.

 

 

만약에 사하구에 사는 사람의 '나이'가 궁금하다면 나이는 2번째 열이므로 column_num에 2를 입력하면됩니다.

=INDEX($B$1:$E$12,MATCH(G3,$E$1:$E$12,0),2)

 

 

 

■ 행에서 기준 값을 찾는 경우(Hlookup과 유사) =INDEX(array,행번호,MATCH함수)

 

이번에는 아래와 같은 표 에서 서구에 사는 사람의 이름, 나이를 구해보겠습니다.

 

 

마찬가지로 array 배열은 B19:M22가 됩니다.

 

또한 이제 기준이 되는 서구, 마포구는 "구"라는 기준으로 묶여있으며,

구 행에서 변화하는 것은 B,C,D… 의 '열'이 변화하고 있습니다.

따라서 MATCH함수는 column_num열에 들어가게 됩니다.

 

마지막으로 우리가 찾고자 하는 이름은 1행에 존재하므로, row_num는 1이 됩니다.

 

따라서 우리는 서구에 사는 사람의 이름은 '신문물'임을 알 수 있습니다.

 

 

마찬가지로 '나이'는 2행이므로, row_num에는 2를 적어주면 됩니다.

=INDEX($B$19:$M$22,2,MATCH(D25,$B$22:$M$22,0))

 

■ 서구 거주자 =INDEX($B$19:$M$22,1,MATCH(D25,$B$22:$M$22,0))
■ 서구 거주자의 나이 =INDEX($B$19:$M$22,2,MATCH(D25,$B$22:$M$22,0))
■ 마포구 거주자 =INDEX($B$19:$M$22,1,MATCH(D26,$B$22:$M$22,0))
■ 마포구 거주자의 나이 =INDEX($B$19:$M$22,2,MATCH(D26,$B$22:$M$22,0))

 

 

 

 

이제 index-match 함수의 기본에 대해 이해가 되셨나요?

 

그럼 다음에는 index-match함수의 다중 조건으로 찾아오겠습니다.

 

2020/10/12 - [사용법/엑셀(EXCEL)] - 내배내적 엑셀 - index-match 함수 기본 응용하기

2020/10/12 - [사용법/엑셀(EXCEL)] - 내배내적 엑셀 - index 와 match 함수 이해하기

2020/10/09 - [사용법/엑셀(EXCEL)] - 내배내적 엑셀 - Vlookup 다중조건

2020/10/07 - [사용법/엑셀(EXCEL)] - 내배내적 엑셀 - Vlookup(feat. 내가 원하는 값을 쉽게 찾자)

2020/09/24 - [사용법/엑셀(EXCEL)] - 엑셀 빠른 실행도구 어디까지 써봤니?(feat. 빠르게 필터링하자)

2020/09/24 - [사용법/엑셀(EXCEL)] - 내배내적 엑셀 - Subtotal (feat.필터링된 숫자의 합만 구하는 법)

2020/09/09 - [사용법/엑셀(EXCEL)] - 내배내적 엑셀 - Countifs 함수