[엑셀] INDEX, MATCH 함수로 특정 값을 찾는 쉬운 방법

「 excel : 특정 값을 찾는 함수 index, match 」

---

안녕하세요.

오늘도 유용한 tip을 드리는 자기입니다.

​

오늘도 엑셀 활용 팁을 전해드리려고 합니다.

며칠 전에 vlookup 함수를 활용하여 특정 데이터를 찾는 방법을 알아보았는데요. 이번에도 전체 목록에서 필요한 값을 찾아내는 다른 방법 index와 match를 활용하는 각각의 방법과 사용법 그리고 예제를 다루어 보도록 하겠습니다.

엑셀에서 index와 match 함수도 vlookup, hlookup, xlookup 못지 않게 꽤 많이 사용되는 함수이므로 숙지해 두면 업무효율이 쑥쑥 올라갈 것입니다.

 

NDEX와 MATCH 함수를 함께 결합하여 사용하면 vlookup 등의 찾기 함수로는 불가능했던 강력한 고급기능을 구현할 수 있다는 장점이 있습니다.

INDEX, MATCH를 따로 분리하여 포스팅할 수도 있지만 이 두 함수는 함께 결합하여 사용할 때 진정한 힘을 발휘하므로 함께 세트로 묶어 배워보도록 합니다.

 

Contents

1. INDEX 함수 2. MATCH 함수 3. INDEX 와 MATCH 함수의 결합

​

- INDEX 함수

INDEX 함수는 테이블이나 특정 범위에서 값 또는 값에 대한 참조를 반환합니다.

​

구문

INDEX(array, row_num, [column_num]) ​ array ▪ 필수 요소입니다. 배열 상수나 셀 범위입니다. ▪ 배열에 행이나 열이 하나만 있을 때는 row_num이나 column_num 인수를 생략할 수 있습니다. ▪ 배열에 행과 열이 두 개 이상 있을 때 row_num이나 column_num만 사용하면 INDEX는 배열의 전체 행이나 열을 하나의 배열로 반환합니다. ​ row_num ▪ column_num이 없는 한 필수입니다. ▪ 값을 반환할 배열의 행을 선택합니다. ▪ row_num을 생략하면 column_num을 지정해야 합니다. ​ column_num ▪ 선택 요소입니다. ▪ 값을 반환할 배열의 열을 선택합니다. ▪ column_num을 생략하면 row_num을 지정해야 합니다.

다음의 예제로 어떤 경우에 어떻게 사용하는지 확인해 보겠습니다.

 

 

위 예제에서 6번째 행, 4번째 열에 있는 값을 찾아보도록 하겠습니다.

위 예제처럼 데이터가 많지 않은 경우에는 그냥 눈으로 세어보면 금방 값을 찾을 수 있습니다만

수백 수천개의 데이터가 있는 경우라면 이야기가 달라지지요. 데이터가 많은 경우에는 눈으로 하나하나 찾는 건 너무 바보같은 짓이지요.

이처럼 많은 데이터에서 특정 위치의 값을 찾아야 할 때에 사용할 수 있는 함수가 바로 INDEX 함수입니다.

​

위 예제의 미션을 해결하기 위해서는 아래의 구문을 사용하면 됩니다.

=INDEX(B5:E11, 6, 4)

첫번째 인자인 B5:E11은 테이블의 범위입니다.

두번째 인자인 6은 테이블에서 6번째 행을 뜻합니다.

세번째 인자인 4는 테이블엣 4번째 열을 의미합니다.

정리하면 범위, 행, 열 의 순서로 입력하면 됩니다.

​

INDEX함수의 결과값으로 정확히 '서예'를 얻어올 수 있었습니다.

​

이상으로 엑셀에서 index 함수는 특정 위치의 값을 구해 오기위해 사용되지만 단순히 그 목적 하나로만 사용되는 경우는 매우 드뭅니다.

index 함수는 단독으로 사용되기보다는 match 함수와 결합하여 사용되는 경우가 대부분이므로 다음의 MATCH 함수도 같이 알아보도록 하겠습니다.

 

 

 

 

- MATCH함수

MATCH 함수는 셀 범위에서 지정된 항목을 검색하고 범위에서 해당 항목이 차지하는 상대 위치를 반환합니다.

​

구문

​MATCH(lookup_value, lookup_array, [match_type]) ​ lookup_value ▪ 필수 요소입니다. lookup_array에서 찾으려는 값입니다. ▪ 예를 들어 전화 번호부에서 어떤 사람의 번호를 찾으려고 할 때 그 사람의 이름을 사용하여 찾지만 원하는 데이터는 그 사람의 전화 번호입니다. ▪ lookup_value 인수는 숫자, 텍스트, 논리값 등의 값이거나 숫자, 텍스트 또는 논리값에 대한 셀 참조일 수 있습니다. ​ lookup_array ▪ 필수 요소입니다. 검색할 셀 범위입니다. ​ match_type ▪ 선택 사항입니다. 숫자 -1, 0 또는 1입니다. ▪ match_type 인수는 Excel 값과 lookup_value 일치하는 방법을 lookup_array. ▪ 이 인수의 기본값은 1입니다.

다음의 예제로 어떤 경우에 어떻게 사용하는지 확인해 보겠습니다.

 

위의 테이블에서 "김구"의 데이터가 있는 행의 위치를 찾으려고 합니다. 행의 위치이므로 리턴값은 숫자가 되겠지요.

index 함수에서는 특정 위치에 있는 값을 찾는 거였다면 match는 특정 값이 있는 위치를 구하는 함수입니다. 서로 반대되는 개념이라과 볼 수도 있습니다.

​

위 예제의 미션을 해결하기 위해서는 아래의 구문을 사용하면 됩니다.

=MATCH("김구", B5:B11, 0)

첫번째 인자인 "김구"는 테이블에 있는 찾으려는 데이터입니다.

두번째 인자인 B5:B11은 테이블에서 찾을 열의 범위를 뜻합니다.

만약 열의 범위에서 찾지 않고 행의 범위에서 찾는다면 B9:E9와 같이 써주시면 됩니다.

세번째 인자인 0은 옵션으로서 정확히 일치하는 데이터를 찾을 때 씁니다.

만약 찾는 값보다 클 때에는 -1을, 작을 때에는 1을 입력합니다.

정리하면 찾을 데이터, 범위, 옵션의 순서로 입력하면 됩니다.

​

위 예제를 실행하여 결과값으로 "5"를 얻을 수 있었습니다. "김구"가 위치한 행은 5번째 행이라는 뜻이죠.

 

 

 

 

- INDEX와 MATCH함수의 결합

 

이제 위에서 알아본 index와 match 함수를 이용하여 보다 실제 업무에 활용되는 사용법을 알아보겠습니다.

다음의 표에서 "김구"의 취미가 무엇인지 index와 match를 결합 활용하여 알아보는 방법입니다.

 

이름이 "김구"인 회원의 "취미"가 무엇인지 찾아보려고 해요.

앞에서도 말씀드렸지만 위처럼 데이터가 몇개 없는 경우에는 굳이 index match함수를 사용하지 않아도 되지만

데이터가 수백 수천개가 있따면 이야기가 달라지죠. 데이터가 많을 때 어떤 조건에 부합하는 값을 찾아야 할 때 쉽게 찾을 수 있는 방법 지금부터 시작합니다.

​

=INDEX(범위, MATCH(찾을 값, 찾을 값 범위, 0), MATCH(찾을 값, 찾을 값 범위, 0) )

INDEX 함수를 기본 골격으로 하여 그 인자에 들어가는 행번호, 열번호를 MATCH 함수로 얻은 값을 넣어주도록 한 것입니다.

다시 설명하면 MATCH함수를 이용하여 "김구"가 위치한 5행을 찾고, "취미"가 있는 4열을 찾아서 각각 index 함수의 두번째, 세번째 인자로 사용하는 것입니다.

위 함수에 실제 동작되는 값을 대입하면 아래와 같습니다.

​

=INDEX(B5:E11, MATCH("김구", B5:B11, 0), MATCH("취미",B4:E4,0))

두번째 인자인 MATCH("김구", B5:B11, 0) 의 결과는 5가

세번째 인자인 MATCH("취미",B4:E4,0) 를 통해 4를 얻을 수 있습니다.

따라서 실제 아래와 같은 동작이 되는 것이죠

=INDEX(B5:E11, 5, 4)

​

 

이렇게 김구의 취미가 무엇인지 index를 사용하되 그 안에 들어가는 인수값은 직접 숫자를 써주는 대신 보다 유연하게 동작되도록 MATCH함수를 넣어줌으로써 데이터를 빨리 구해낼 수 있습니다.

​

이와 같이 엑셀에서는 다른 함수와 혼용하여 사용함으로써 시너지를 내는 함수를 구현해 낼 수 있습니다.

​

​

​

이상으로 오늘은 Excel함수에서 Index, Match를 활용하여 특정 조건에 부합하는 결과값을 얻어내는 방법을 알아보았습니다.

이렇게 현명한 방법으로 업무효율을 높여 보시기 바랍니다.

​

읽어주셔서 감사합니다.