엑셀 실무. Index, Match 함수 기초

일하시는 곳에서 여러 가지 데이터를 취합하다 보면 엑셀을 사용하게 됩니다. 이번 포스팅에서는 내가 취합한 데이터 중 원하는 데이터를 찾기 위해 사용하는 함수 중 Index와 Match 함수를 이용해 원하는 데이터를 찾을 수 있는 아주 기초적인 내용에 대해서 다뤄보겠습니다. 

 

 

목차

 

1. 함수 활용법

 

Index와 Match 함수를 이용해 데이터를 찾을 때는 이것 하나만 기억하시면 됩니다. 

먼저 빵의 종류별 가격을 알고 싶다고 가정하면, 바게트는 얼마? 치아바타는 얼마? 이렇게 정보를 찾아보셔야겠죠?

그럼 엑셀 파일의 셀에 빵의 종류를 치면, 빵의 가격이 저절로 검색돼서 나오는 것을 원하시게 될 겁니다. 그럴 때 엑셀에 이렇게 작성을 해주시면 됩니다. 

 

=index(빵의 가격 정보가 있는 열 또는 행, match(빵의 이름을 검색할 셀, 빵 종류가 나열되어 있는 열 또는 행, 0)) 

 

이렇게만 적으면 이해하기가 힘드시겠죠? 

그림으로 보면서 차근차근 따라 해보시면 됩니다. 

 

 

1) 먼저 아래 그림처럼 데이터가 있는 셀들이 있을 거예요. 

 

 

2) 그럼 정보를 찾았을 때 가시성이 좋게 하기 위해 다른 셀에 우리가 볼 정보를 표시할 수 있도록 표를 만들어 주겠습니다. 

 

 

3) 그럼 이제 빵 종류 바로 밑에 빵이름을 넣어주면 옆에 가격이 자동으로 뜨도록 만들어주겠습니다. 

 

먼저 우리가 찾고 싶은 정보는 빵의 가격입니다. 그래서 먼저 [index 입력] > [가격 정보가 있는 셀 지정] 이 순서대로 함수를 작성해 주는 것입니다. 우리는 엑셀에게 이 셀에서는 가격을 표시할 거야!라는 명령을 내려주는 것입니다. 

 

 

그리고 어떤 빵의 가격을 알고 싶은 것인지 지정을 해주겠습니다. 우리는 셀에 빵 이름을 입력하면 가격이 나올 수 있도록 지정을 해줄 것이기 때문에, 빵 이름을 쓸 셀을 먼저 지정해 줍니다. 이 때 셀을 고정시키려면 셀을 지정한 뒤에 F4를 눌러주시면 절대참조로 지정이 됩니다. 

 

 

그리고 빵 이름이 저장되어 있는 구간이 어디인지 지정을 해주겠습니다. 바게트부터 바나나빵까지 이름이 쭈욱 나열되어 있는 것이 보이시죠? 이 구간을 마우스로 드래그해서 범위를 지정해줍니다. 이때에도 범위를 지정한 뒤, F4 키를 눌러주면 절대참조로 바뀝니다. 

 

 

그리고 찾고 싶어서 입력한 정보와 정확하게 일치하는 값을 찾으려면 '0'을 입력해줘야 합니다. 이렇게 MATCH 함수를 작성하면 E3에 가격이 알고 싶은 빵의 이름을 적을 건데, 그 빵의 이름을 B3와 B10 사이에서 검색해서, 정확하게 일치하는 값으로 찾아줘!라고 명령을 해준 것입니다. 

 

 

이렇게 함수 입력이 끝나면 가격 부분에 정보가 없다는 표시가 나타납니다. 이건 우리가 빵의 이름을 적어놓지 않아서 그래요. 그럼 가격 왼쪽 셀에 빵의 이름을 적어보겠습니다. 

 

 

저는 "브리오슈의 가격이 알고 싶어요"라고 명령을 해봤습니다. 그래서 브리오슈를 입력했더니 바로 가격을 찾아서 알려줍니다. 

 

 

INDEX와 MATCH 함수, 기본적으로 정말 쉽죠? 

 

 

2. 함수의 구성

 

1) INDEX

INDEX 함수

=INDEX(배열, 행번호, [열번호], [배열번호]) [참조형] 또는

=INDEX(배열, 행번호, [열번호]) [배열형]

 

INDEX 함수는 기본적으로 내가 지정해 놓은 범위의 몇 행, 몇 열에 있는 데이터 값을 불러오는 함수입니다.

그래서 일반적으로 INDEX 함수는 행번호와 열번호를 적어서 원하는 데이터를 찾아주게 됩니다. 또한 INDEX 함수는 데이터를 더해주는 SUM 함수와 데이터의 개수를 세어주는 COUNTIF 함수와 함께 유용하게 활용할 수 있으니, 잘 활용해 보시면 좋습니다. MATCH 함수는 어떻게 사용할까요?

 

2) MATCH

MATCH 함수

=MATCH(찾을 값, 범위, [일치옵션])

 

MATCH 함수에서 일치옵션은 대부분 정확히 일치를 사용하고 있기 때문에 숫자 0으로 표기해 줍니다. 유사일치를 사용하는 경우도 있긴 하지만, 구간별 데이터로 뽑는 경우가 아니고서는 잘 사용하지 않아서 일단 이 포스팅에서는 다루지 않았습니다.