엑셀 INDEX와 MATCH 함수

 

안녕하세요. BIG MAN입니다.

 엑셀을 사용하면서 자주 사용하지 않지만, 가장 강력한 조회 기능을 제공해 주는 함수가 바로 INDEX 함수와 MATCH 함수입니다. 두 개의 함수를 단일 함수로 사용해도 좋지만, 함께 사용한다면 더 강력한 모습을 보입니다. 오늘은 INDEX 함수와 MATCH 함수에 대해 알 아보겠습니다.

참조 파일 다운로드

INDEX_MATCH.xlsx

다운로드

---

 

 

INDEX 함수

 INDEX 함수는 셀 범위나 배열 범위에서 지정한 위치에 있는 값을 가져오는 함수입니다. 여기서 지정한 위치는 마치 좌표값과 같습니다. 예를 들어 아래와 같이 x, y 2차원 좌표가 있다면 좌표의 값들은 참조 범위이고 y=20, x=40은 행 번호와 열 번호이며 여기서 만나는 위치의 값을 가져다주는 방식이기 때문입니다. 🔻

BIG MAN:IT

Index 함수의 구문은 아래와 같습니다.

 

=INDEX(array, row_num, column_num)

 

각각의 인수에 대하여 알아보겠습니다.

 

array : 참조 범위입니다. -> 위 좌표의 x, y 모든 값에 해당합니다.

row_num : 참조 범위에서 행 번호입니다. -> 위 좌표에서는 y 값에 해당합니다.

column_num : 참조 범위에서 열 번호입니다. -> 위 좌표에서는 x 값에 해당합니다.

☞row_num, column_num는 모두 상대 위치 값을 입력해야 합니다.

 

예를 들어서 한번 적용해 보겠습니다. 아래 표에서 2016년도 딸기의 가격을 알고 싶습니다. 참조 범위는 C4:E6까지 이며, "2016년"(row_num, y 좌표)은 상대 위치가 참조 범위에서 봤을 때 2번째에 위치하므로 숫자 "2"를 입력하고, "딸기"(column_num, x 좌표)의 상대 위치는 참조 범위에서 봤을 때 3번째에 위치하므로 숫자 "3"을 입력하면 됩니다. 정리하여 입력하면 아래와 같습니다. 🔻

 

=INDEX(C4:E6,2,3)

결과 값 : 15000

BIG MAN:IT

 

MATCH 함수

 MATCH 함수는 참조 범위에서 지정한 값과 일치하는 셀의 상대 위치를 반환합니다. MATCH 함수의 경우 INDEX 함수와 함께 참조 범위의 상대 위치를 찾을 때 주로 사용을 하는 편입니다. INDEX와 헷갈려하시는 분들이 꾀있습니다. MATCH 함수는 30cm 곧은 자라고 생각하시면 헷갈리지 않습니다. 아래 예시를 보시면 왜 곧은 자라고 이야기하는지 알 수 있습니다.🔻

BIG MAN:IT

MATCH 함수의 구문은 아래와 같습니다.

 

=MATCH(lookup_value, lookup_array, match_type)

 

각각의 인수에 대하여 알아보겠습니다.

 

lookup_value : 참조 범위(lookup_array)에서 찾을 값입니다.

lookup_array : 참조 범위입니다. 단일 행 또는 단일 열을 선택해야 합니다.

match_type : 정수 -1,0,1을 지정할 수 있으며, 일치 여부 판단하는 방식을 결정합니다.

 

match_type이 -1 이면 lookup_value와 비교하여 최솟값의 위치를 반환하고, 1이면 최댓값의 위치를 반환합니다. 0은 정확하게 일치하는 값의 위치를 반환합니다. 또한 최솟값을 찾을 경우는 lookup_array는 내림차순으로 정렬되어야 하고, 최댓값을 찾는 경우는 lookup_array는 오름차순으로 정렬되어 있어야 합니다.

 

예를 2015년~2017년까지 참조 범위(lookup_value)에서 "2016년"의 상대 위치 값을 찾을 때는 아래와 같이 찾을 값인 "2016년"을 입력하거나 셀 참조를 선택하고 참조 범위를 단일 행이나 단일 열로 선택을 합니다. 예제는 단일  열로 선택하였습니다. 그리고 마지막으로 일치 조건을 "정확히 일치"하는 값으로 선택하였습니다. 정리하여 입력하면 아래와 같습니다. 🔻

 

=MATCH("2016년",B2:B4,0)

결과 값 : 2

BIG MAN:IT

 

INDEX + MATCH 응용하기

 INDEX와 MATCH 함수를 함께 사용해 보겠습니다. 두 가지를 함께 사용할 경우 정말 강력한 조회 기능을 제공합니다. 아래 예제와 같이 연도별, 과일 종류별로 가격이 입력되어있는 표가 있습니다. 원하는 년도에 원하는 과일의 가격을 정확하게 알고 싶습니다. 조금 과장해서 약 10년 동안 2,000 가지의 과일이 있다고 생각해 보겠습니다. 약 20,000개의 자료 중 하나를 조회해야 합니다. 🔻

BIG MAN:IT

INDEX의 row_num와 column_num는 행과 열의 상대 위치를 나타낸다고 했습니다. 그리고 MATCH는 셀의 상대 위치를 반환한다고 했습니다. 따라서, INDEX의 row_num와 column_num에 MATCH 함수를 응용한다면 아주 손쉽게 20,000개의 자료를 조회할 수 있습니다.

 

그럼 아래와 같이 입력할 예정입니다.

 

=INDEX(참조 범위 지정, MATCH(년도 상대 위치 찾기), MATCH(과일 종류 상대 위치 찾기))

 

첫 번째로 MATCH 함수를 이용하여 INDEX의 row_num의 행 번호를 찾아보겠습니다. row_num는 위에서 정한 것처럼 연도의 상대 위치를 구하는 것입니다. 🔻

 

=MATCH(G3,B4:B6,0)

결과 값 : 2

BIG MAN:IT

 

MATCH 함수 예제와 거의 동일합니다. 단지 셀 참조했을 뿐입니다. 이렇게 사용자 값을 입력받는 함수들은 보통 셀 참조를 많이 사용하므로 이런 부분은 빨리 이해를 해주셔야 합니다.

 

 

 

 

두 번째로 MATCH 함수를 이용하여 INDEX의 column_num의 행 번호를 찾아보겠습니다. column_num는 위에서 정한 것처럼 과일 종류의 상대 위치를 구하는 것입니다. 🔻

 

=MATCH(H3,C3:E3,0)

결과 값 : 3

BIG MAN:IT

 

이제 row_num와 column_num의 행 번호와 열 번호를 모두 찾아냈습니다. 🔻 이제는 입력하는 값에 따라 자동으로 행 번호를 알려 줍니다. 이것을 INDEX 함수에 적용시켜보겠습니다.

BIG MAN:IT

 

 

=INDEX(C4:E6,MATCH(G3,B4:B6,0),MATCH(H3,C3:E3,0))

 

다시 간단하게 표현한다면 아래와 같습니다. 🔻

BIG MAN:IT

 

최종 결과는 아래와 같습니다. 🔻

 

BIG MAN:IT

 

INDEX와 MATCH 함수는 자주 사용하지 않기 때문에 어려워하시거나 헷갈려하시는 분들이 많이 있습니다. 그래서 저는 INDEX는 처음 설명했던 것처럼 x, y 좌표를 생각하고, MATCH 함수는 30cm 곧은 자를 생각합니다. 그리고 MATCH 함수에서 주의할 사항은 단일 행과 단일 열을 중 하나를 선택해야 한다는 것입니다. 이점만 주의한다면 INDEX 함수와 MATCH 함수를 이용한 조회 기능을 구현하는데 어려움이 없을 것이라 생각합니다.

 

INDEX와 MATCH 함수는 지면으로 다 설명하기 힘들 만큼 더 상세한 내용들이 있지만, 여기에서는 가장 많이 사용하는 범위로 끝을 내겠습니다. 나중에 좀 더 상세한 내용으로 찾아뵙겠습니다.

 

그리고 잘 못된 부분이나 추가 문의 사항은 댓글 남겨주시기 바랍니다. 감사합니다.