내가 원하는 데이터 매칭하여 찾기
- INDEX
- MATCH
- INDEX-MATCH 함수 활용하기
아무도 알려주지 않아서 실무에서 혼자 맨땅에 헤딩하면서 습득한 내용을 다룹니다.
(최적화된 방법보다는 그냥 제가 할 줄 아는 방법을 다룬 것이라 이 블로그에 있는 방법이 가장 효율적인 방법은 아닐 수 있습니다!)
안녕하세요 데이빗입니다.
지난 포스팅에서 제 구글 스프레드(그리고 엑셀) 인생에서 첫 충격을 가져다준 VLOOKUP 함수에 대해 알아봤습니다.
이번 포스팅에서는 그보다 한참 뒤에 깨닫게 된, VLOOKUP과 비슷하게 활용할 수 있지만, 더 이용하기 좋은 INDEX-MATCH 함수에 대해 알아보도록 하겠습니다.
우선 오해하시거나 불편해하시는 분이 있을까 봐 먼저 짚고 넘어가겠습니다.
구글 스프레드시트에는 INDEX-MATCH라는 함수가 없습니다.
INDEX라는 함수와 MATCH라는 함수를 조합하여 VLOOKUP과 동일한 작업을 할 수 있게 해주는 것입니다.
그러면 그냥 VLOOKUP을 이용하면 되지 않느냐?라는 질문이 있을 수 있어, 왜 INDEX-MATCH 함수를 이용하는 것이 더 좋은지 아래에서 설명하도록 하겠습니다.
그러면 구글 스프레드시트에서 INDEX-MATCH 함수를 활용하기에 앞서, 이 함수를 구성하는 INDEX 함수와 MATCH 함수에 대해 알아보도록 하겠습니다.
INDEX 함수
셀(Cell)에 '=INDEX('를 입력하면 아래와 같이 설명이 나옵니다.
참조하고자 하는 범위를 설정하고, 행(Row) 값과 열(Column) 값을 입력해주면 해당 범위에서 입력해 준 행(Row)과 열(Column)에 입력된 값을 출력해주는 함수입니다.
위 이미지와 같이 C3부터 E7까지를 참조 범위로 설정하고, 행(Row) 값을 1, 열(Column) 값을 2로 넣어 주었습니다.
그러면 C3부터 E7까지의 범위 내에서 첫 번째 행(Row)인 3행, 두 번째 열(Column)인 D열을 가져와 D3의 값을 출력해 줍니다. (과일)
만약 같은 범위에서 행(Row) 값과 열(Column) 값을 각각 2와 3으로 했으면, E4의 값인 '노란색'을 출력해 줄 것입니다.
MATCH 함수
다음으로 MATCH 함수입니다.
셀(Cell)에 '=MATCH('를 입력하면 아래와 같이 설명이 나옵니다.
검색할 키(Key) 값을 입력하고, 범위를 설정해 줍니다.
이때, 범위는 하나의 행(Row) 또는 하나의 열(Column)로 설정해 줄 수 있습니다.
만약에 하나의 열(Column)으로 설정해 줬다고 가정할 때, 키(Key) 값으로 입력해 준 데이터가 범위로 설정한 열(Column)의 몇 번째 행에 위치하는지를 찾아 그 값을 출력해 줍니다.
[검색_유형]은 0으로 입력합니다. 이 값은 키(Key) 값으로 검색할 때 정확한 값을 찾아주는 기능으로, VLOOKUP의 FALSE와 유사하게 생각해주시면 됩니다.
위 이미지와 같이 키(Key) 값에 '애호박'을 입력하고, 범위를 C3부터 C7까지, 즉 C열(Column)로 설정해 주었습니다.
C3부터 C7까지의 범위에서 '애호박'이라는 데이터는 네 번째 행(Row)에 위치하고 있기 때문에 4를 출력해 줍니다.
당연히 '애호박'이라는 값을 직접 입력하지 않고 C6을 참조로 걸어주어도 동일한 효과를 볼 수 있습니다.
여기까지 해서 INDEX함수와 MATCH 함수에 대해 알아보았는데요, 정리하자면 INDEX 함수는 설정한 범위에서 특정한 행(Row)과 열(Column) 값을 입력해 주었을 때 해당 위치의 값을 불러와주는 함수이고, MATCH 함수는 내가 찾고 싶어 하는 키(Key) 값이 설정한 범위에서 몇 번째 행 또는 열에 위치하는지를 찾아 그 값을 불러와주는 함수입니다.
이 두 가지 특성을 잘 조합해주면 아래와 같이 활용할 수 있습니다.
내가 찾고 싶어 하는 키(Key) 값이 하나의 범위에서 몇 번째 행(Row)에 위치하는지를 찾아 같은 행(Row)에 있는 또 다른 열(Column)의 값을 불러와준다.
즉, VLOOKUP과 같은 기능을 하는 함수 조합을 만드는 것입니다.
아래 예제에서 확인해 보도록 하겠습니다.
구글 스프레드시트 예제 >> 링크
P열(Column)에서 내용 확인하실 수 있습니다.
먼저 MATCH 함수를 이용해서 내가 찾고 싶어 하는 키(Key) 값이 행(Row) 정보를 불러올 수 있도록 해주겠습니다.
검색할 키(Key)는 K열(Column)에 있는 물품명입니다.
범위는 C3부터 E7에 있는데, 우리는 키(Key) 값이 포함된 열(Column)만 가져오면 되므로, C3부터 C7까지 잡겠습니다.
마지막으로 검색유형은 위에서 알아본 것과 같이 0으로 하겠습니다.
이때, MATCH 함수의 결괏값으로 설정한 범위에서 '사과'라는 키(Key) 값이 첫 번째 행에 있으므로 결괏값 1을 반환합니다.
다음으로 INDEX 함수를 이용해서 MATCH 함수에서 키(Key) 값으로 설정한 '물품'에 대한 대응되는 '분류'값을 불러오도록 하겠습니다.
Q열(Column)에서 내용 확인하실 수 있습니다.
'물품'에 대응해서 가져오고자 하는 값은 '분류'에 있으므로, 범위를 D3부터 D7으로 설정해 줍니다.
'행' 값으로 위에서 MATCH 함수로 설정한 셀(Cell)인 P3를 참조해 줍니다.
'열' 값은 단일 열(Column)을 범위로 잡았기 때문에 별도로 입력하지 않아도 됩니다.
이렇게 하면 키(Key) 값에 따라 MATCH 함수가 행 값을 반환하고, 반환된 행 값을 기반으로 INDEX 함수가 가져오고자 하는 데이터 범위에서의 값을 반환해 줍니다.
마지막으로, 이 두 가지 작업을 하나의 셀(Cell)에서 하기 위해 위의 INDEX 함수에서 참조한 P3 대신 MATCH 함수 부분을 넣어줍니다.
해당 내용을 복사할 때, 참조하는 범위가 변하지 않도록 절대참조로 바꿔주는 것 잊지 마세요.
* 절대참조 하는 방법이 궁금하다면?! >> 구글 스프레드시트 - 초급편 (4)
이렇게 하여 INDEX-MATCH 함수 조합을 만들 수 있습니다.
최종적으로 O열(Column)과 R열(Column)을 비교해보면 같은 결괏값을 출력해 주는 것을 볼 수 있습니다.
여기까지 잘 따라오셨다면, 맨 처음 있었던 질문 '그러면 그냥 VLOOKUP을 이용하면 되지 않느냐?'에 대한 답을 드리도록 하겠습니다.
다른 분들은 모르겠지만, 제가 같은 기능을 구현하고자 할 때 간편해 보이는 VLOOKUP보다 INDEX-MATCH를 선호하는 이유는 변화에 더 유연하게 대응할 수 있기 때문입니다.
만약 범례로 사용하는 C3에서 E7에 변화가 생긴다고 가정해 봅시다.
현재는 C-E 열(Column)에 각각 물품명, 분류, 색깔 정보가 들어있는데, 추가로 가격이나 구매처 등의 정보를 기재한다고 하면 열(Column) 정보에 변화가 생길 것입니다.
만약 C열(Column)의 물품명과 D열(Column)의 분류 사이에 가격 정보가 들어가도록 변경한다고 하면, 열(Column) 삽입을 이용해서 빈 열(Column)을 만들고 가격 정보를 입력했다고 가정해 봅시다.
그럴 경우, VLOOKUP에서 설정한 범위는 자동으로 C3:E7에서 C3:F7으로 변경될 것이고, INDEX-MATCH에서 분류값을 설정한 범위도 자동으로 D3:D7에서 E3:E7으로 변경될 것입니다.
하지만 여기에서 자동으로 변하지 않는 것이 있는데, 바로 VLOOKUP의 색인 값입니다.
열(Column) 삽입을 해줘도 VLOOKUP의 색인 값은 자동으로 2에서 3으로 변하지 않기 때문에 분류 값을 반환해주던 VLOOKUP 함수는 C3:F7 범위에서 색인 2가 가리키는 두 번째 열의 가격 정보를 반환하게 됩니다.
해당 내용은 위 예제 내용을 복사해서 실제로 해 보시면 쉽게 확인하실 수 있을 것입니다.
이번 포스팅에서는 INDEX-MATCH 함수에 대해 알아보았습니다.
다음 포스팅에서는 추가 함수를 알아보기 전에, 구글 스프레드시트에서 볼 수 있는 다양한 오류값들과 오류값의 원인, 해결 방안에 대해 알아보도록 하겠습니다.
'구글 스프레드시트' 카테고리의 다른 글
| 구글 스프레드시트 - 중급편 (4) (중복되는 데이터 정리하기) (0) | 2023.06.10 |
|---|---|
| 구글 스프레드시트 - 중급편 (3) (구글 스프레드시트의 오류 이해하기) (0) | 2023.01.04 |
| 구글 스프레드시트 - 중급편 (1) (VLOOKUP 함수 이해하기) (0) | 2022.12.22 |
| 구글 스프레드시트 - 초급편 (11) (WEEKNUM 함수 이해하기) (0) | 2022.11.03 |
| 구글 스프레드시트 - 초급편 (10) (TODAY, WEEKDAY 함수 이해하기) (0) | 2022.10.18 |
댓글