엑셀 INDEX 함수 및 엑셀 MATCH 함수 사용 방법 (다양한 예제)

엑셀 INDEX 함수 및 MATCH 함수는 복잡한 검색과 조회를 수행하기 위해 가장 많이 사용되는 함수입니다. INDEX 함수와 MATCH 함수를 활용하는 가장 좋은 방법은 바로 양방향 검색입니다. 예제와 함께 INDEX, MATCH함수 사용방법 살펴보겠습니다.

엑셀 - INDEX 함수 - MATCH 함수 - 사용방법

 

오늘은 엑셀 함수 INDEX와 MATCH를 함께 사용하여 검색 및 조회하는 사용 방법을 알아보겠습니다. 먼저 INDEX 함수 상용방법을 알아보고, MATCH 함수도 사용방법을 익힌 다음, 두 함수를 결합하여 좀 더 복잡하고 유용한 검색 및 조회하는 방법을 다양한 예제와 함께 알아보겠습니다. 

 

엑셀 INDEX 함수 사용 방법

엑셀 INDEX 함수 구문

INDEX(array,row_num,[col_num])

• array - 조회할 셀의 범위
• row_num - 조회할 행의 위치
• col_num - [optional] 조회할 열의 위치

 

엑셀 함수 INDEX 활용 예제

엑셀 INDEX 함수는 선택된 범위 내에서 특정 값을 검색합니다. 예제를 보면서 같이 이해해보겠습니다. 아래 예제는 태양계의 행성들을 있는 순서대로 나열한 것입니다. 그리고 각 행성의 지름 그리고 각 행성이 가진 위성갯수를 함께 가지고 있습니다. 우리는 4번째에 있는 행성이 무엇인지 궁금하여 그 이름을 INDEX 함수를 이용해서 가져와 본다고 가정해보겠습니다. 이 경우 아래 예제와 같이 =INDEX(B3:B11, 4)의 수식을 이용하여 태양계 네번째 행성은 '화성'이라는 사실을 검색/조회할 수 있습니다. 

 

 

엑셀 - INDEX 함수 사용방법 예제

만약 같은 표 안에서 INDEX 함수로 화성의 지름을 구하려면 어떻게 해야 할까요? 이 경우 INDEX 함수 인자값에 행 번호와 열 번호를 모두 입력해주면 됩니다. 아래 예제를 보시면 =INDEX(B3:D11,4,2)와 같이 4번째 행의 2번째 열의 값을 조회하도록 설정한 걸 보실 수 있습니다. 엑셀 INDEX 함수를 사용할 때 이처럼 행번호는 필수 입력값이며, 만약 첫번째 열이 아닌 다른 열의 값을 반환해야할 때는 열번호까지 함께 지정하여 사용할 수 있습니다.  

엑셀 - INDEX 함수 사용방법 예제2

 

엑셀 MATCH 함수 사용 방법

엑셀 MATCH 함수 구문

=MATCH(lookup_value, lookup_array, [match_type])

• lookup_value - 검색하고자 하는 값
• lookup_array - 검색을 할 셀의 범위
• match_type - [optional] 검색 타입 (1 = 보다 작음 (default), 0 = 정확히 일치, -1 = 보다 큼.)

 

엑셀 함수 MATCH 활용 예제

엑셀 MATCH함수 사용 방법을 더 구체적으로 알아보기 위해 아래의 예제를 함께 보겠습니다. 과일 리스트가 B열에 주어졌고, 우리는 '복숭아'의 위치를 MATCH함수를 이용하여 몇번째에 있는지 검색하고자 합니다. 이 경우 =MATCH(E2, B3:B9)와 같이 MATCH함수에 검색할 값과, 검색할 위치를 주고 복숭아가 3번째에 있다는 것을 구할 수 있습니다. 

 

엑셀 - MATCH 함수 사용방법 예제2

 

엑셀 INDEX 함수 사용시 한가지 주의사항

엑셀 INDEX 함수 사용시 한가지 주의해야할 점이 있습니다. INDEX 함수의 세번째 인자값 match_type의 default 값이 1로 설정되어있습니다. 그래서 세번째 인자값을 입력하지 않으면 자동으로 1로 입력된 것으로 취급합니다. 하지만 1은 '보다 작음' 이기 때문에 정확한 검색을 하지 않고 엉뚱한 값을 반환하는 결과를 가져올 수 있습니다. 그래서 보통 INDEX함수를 '정확히 일치'하는 검색용으로 사용할 때 반드시 마지막에 0을 꼭 붙이는 습관을 들이는게 좋습니다. 

 

엑셀 INDEX 함수와 MATCH 함수 함께 사용하는 방법

엑셀 INDEX 함수와 MATCH 함수의 사용방법을 간단히 알아보았습니다. 이제는 INDEX함수와 MATCH함수를 함께 사용하여 검색과 조회에 유용하게 활용하는 방법을 살펴보겠습니다. 오늘 포스팅의 가장 핵심 부분은 바로 지금부터 나오는 내용입니다. 

 

엑셀 INDEX 함수와 MATCH 함수 함께 사용하는 예제1

표에는 각각의 사람들이 월마다 얼마를 벌었는지 나와있습니다. 여기서 특정 사람의 이름을 가지고 이 사람이 특정 월에 얼마를 벌었는지 검색/조회하고 싶다고 가정해 봅시다. 우리가 앞에서 봤듯이 INDEX 함수는 범위 내에서 행의 위치 열의 위치를 가지고 특정 값을 조회하는 역할을 했습니다. 그리고 MATCH 함수는 특정 값이 어느 위치에 있는지 조회하는 함수였습니다.

 

그럼 이 두가지를 조합해보면, 우리는 MATCH함수를 가지고 특정 사람의 이름이 이 표에서 몇번째 행에 위치하는지 그 위치값을 조회할 수 있고, 그걸 다시 INDEX에 함수 인자값으로 넣어주면 최종적으로 우리가 원하는 '얼마 벌었는지'를 조회할 수 있게 됩니다. 여기서도 마찬가지로 MATCH함수를 사용시에 세번째 인자값은 0으로 세팅이 되었는지 다시 한번 확인하시기 바랍니다.

엑셀 - INDEX 함수- MATCH 함수 사용방법 예제

 

엑셀 INDEX 함수와 MATCH 함수 함께 사용하는 예제2

우리는 INDEX 함수와 MATCH 함수를 이용해서 행과 열을 동시에 검색하는 양방향 검색도 할 수 있습니다. 다시한번 INDEX 함수의 원리를 떠올려보면, INDEX 함수는 행과 열의 위치값을 주고 범위 내에서 특정 값을 조회해오는 역할을 했습니다. 그리고 MATCH 함수는 특정 값이 있는 위치(순서)를 반환했습니다. 그럼 MATCH 함수로 행의 위치를 한번 가져오고, 열의 위치를 또 가져오고 두개를 INDEX 함수 인자값으로 사용해주면 양방향 검색이 가능하겠죠?

 

아래 예시를 보겠습니다. '현숙'이 '3월'에 얼마를 벌었는지 조회해보는 예제입니다. 먼저 MATCH(H2,B3:B10,0)을 통해 현숙의 행 위치(순서)를 구하고 또 MATCH(H3, C2:E2),0)을 통해 '3월'의 열의 순서를 구합니다. 이 두개를 INDEX 함수 안에 넣어주면 아래와 같이 양방향 검색이 가능해집니다. 

 

 

엑셀 - INDEX 함수- MATCH 함수 사용방법 예제2

지금까지 엑셀 INDEX 함수, 엑셀 MATCH 함수를 이용하여 데이터 분석시 검색과 조회에 활용할 수 있는 방법에 대해 알아보았습니다. 특히 마지막으로 본 양방향 검색은 알아두면 정말 유용하게 사용할 수 있기 때문에 예제를 보시면서 직접 엑셀에서 따라해보시는걸 추천드립니다. 다른 엑셀 꿀팁, 엑셀 업무 활용 방법에 대해 궁금하시다면 '엑셀' 카테고리 내 다양한 엑셀 관련 글들을 참고하세요. 

 

 

엑셀 선형보간법(interpolation) 계산하는 방법 (중간값 추정하는 방법)

 

엑셀 ROUND 함수 정리 (ROUNDUP, ROUNDDOWN 함수 비교)