INDEX/MATCH 활용[엑셀/구글스프레드시트]
자동으로 값을 매칭시켜 값을 불러와보자
Google SpreadSheet 데이터 분석 강의를 하며 쿼리를 집중적으로 가르치지만, 부분적으로 다른 함수도 설명해주고 있다. 그 중에서 가장 유용한 함수를 하나 꼽자면 MATCH 함수를 꼽을 수 있다.
MATCH 함수는 홀로 쓰일 때는 그 빛을 발휘하지 못하지만, INDEX 함수나 다른 함수와 같이 쓰일 경우 번거로운 작업을 크게 줄여줄 수 있는 매력적인 함수이다.
우선 아래에서 MATCH 함수에 대한 설명과 예시를 살펴보도록 하자.
1. MATCH 함수 설명
Syntax
- 특정 범위(Range)에서 특정 값(Search_key)이 매칭되는 위치를 출력한다.
EX)MATCH("Sunday",A2:A9,0)
MATCH(search_key, range, [search_type])
search_key - 찾아야 할 값. 예시, 42, "Cats", or I24.
range - search key 가 속한 범위를 정해주면 된다. 범위는 1보다 무조건 커야하며, 그렇지 않으면 N/A 출력.
search_type - [ OPTIONAL - 1 by default ] - The manner in which to search.
1, 기본값, 범위를 오름차순으로 정렬한 후 search_key 이하 중 가장 큰 값을 출력.
0 정확한 값을 찾으며, 범위가 정렬되지 않음
-1 범위를 내림차순으로 정렬한 후 search_key 이상의 값 중 가장 작은 값을 출력
출처.
https://support.google.com/docs/answer/3093378?hl=en
MATCH 함수는 특정 범위에서 찾아야 하는 값이 몇 번째 있는지를 출력하는 함수이다.
아래 예시를 보며 확인해보자.
아래 범위를 보면 B4:B10까지 영어 알파벳이 쓰여져 있는 것을 확인할 수 있고, B4는 1행, B10은 7행으로 총 7개의 행으로 구성된다.
만약 B6에 위치한 C 값의 위치가 B4부터 B10 중에서 몇 번째 있는지 알고 싶다면, 아래의 수식을 입력할 수 있다.
1) MATCH("C", B4:B10,0) - 고정값 텍스트 "C"를 Search_key 활용
2) MATCH(B6, B4:B10,0) - 변동값 셀 주소 B6를 Search_key 활용
(고정값과 변동값을 활용하여 두 가지 형태로 불러올 수 있는데, 대부분은 셀 주소를 활용하여 값을 불러오게 해 복사 붙여넣기를 하여 자동으로 수식을 완성시키는 형태가 많다.)
"C"는 B4:B10의 3번째 행에 위치하므로, 3이라는 값이 나오는 것을 확인할 수 있을 것이다.
다만 여기서 주의해야할 점은 3번째 인자인 [Search_type]을
0으로 해줘야 정확한 값을 매칭해서 불러온다는 것이다.
만약 0을 쓰지 않고, 빈값을 쓸 경우 기본값인 1이나 -1 값이 입력되면 내가 원한 값이 아닌 가장 근사한 값을 매칭해 불러올 수 있다는 것을 꼭 유념하도록 하자.
그럼 MATCH 함수와 궁합이 잘 맞는 INDEX 함수에 대해서도 알아보자.
2. INDEX 함수 설명
Syntax
행과 열을 인식하여, 범위에서 행과 열에 속하는 값을 출력한다.
EX) INDEX(A1:C20, 5, 1)
INDEX(reference, [row], [column])
reference - 값이 불러져오는 셀의 범위.
row - [OPTIONAL - 0 by default] - reference 범위에서 불러져오는 행 순서.
column - [OPTIONAL - 0 by default] - reference 범위에서 불러져오는 열 순서.
출처.
https://support.google.com/docs/answer/3098242?hl=en
인덱스 함수는 벡터범위에서 그에 해당하는 행과 열에 해당하는 값을 가져오는 함수이다.
예시를 위해 아래 데이터를 보도록 하자.
위 데이터는 영어에 해당하는 한글의 값을 Pair 시켜 놓은 데이터이다.
B4:C10까지를 범위라고 할 때 각 노란색으로 칠해진 "나"의 값을 불러오려면 어떻게 해야할까?
범위는 (1,1)부터 (7,2)까지 총 7X2 행렬로 보면 될 것이다.
B4:C10의 범위 중 2행 2열에 속하는 것이 "나"에 해당하기 때문에 아래와 같이 수식을 넣어주면 된다.
INDEX(B4:C10, 2, 2)
여기서 범위 값인 "B4:C10"을 reference 범위에 넣어주고, Row는 2행에 속하기 때문에 2값을 넣어주고, Column에도 역시 2열에 속하므로 2 값을 넣어주면 Reference 범위에서 2행 2열에 속하는 "나"라는 값을 출력하게 되는 것이다.
3. INDEX/MATCH 활용
그럼 INDEX/MATCH를 활용하여 1)DATA에 있는 값을 2)Dashboard에 옮기고 싶을 경우 어떻게 하면 될까?
우선 Dashboard를 살펴보면 아래 4가지 데이터를 가지고 있는 것을 확인할 수 있다.
1) Reference
2) Data
3) Cost
4) PIC
우리는 Reference 넘버를 Search_key로 삼아 Reference 넘버에 해당하는 값의 Date, Cost, PIC를 자동으로 불러오는 DashBoard를 만들어 보도록 하겠다.
우선 INDEX 부분을 살펴보도록 하자.
Reference 값이 "OB-24-1"이라고 가정할 때 Date에 해당하는 값이 나오기 위해서는 A16:E19의 범위 중 (2,4)에 해당하는 값이 나와야 한다.
*A16인 Reference가 (1,1)이고 E19 값은 (4,5)로 총 4x5 행렬로 이루어져있다.
먼저 생각해보아야 할 것은 바로 Search_Key를 무엇으로 두느냐는 것이다.
행에 해당하는 Match함수
여기서 Search_Key는 "OB-24-1" 값인 B22가 될 것이다. 왜냐하면 Reference가 속하는 행을 살펴보면 OB-24-1이 2번째 열에 속하기 때문이다.
MATCH(B22,$A$16:$A$19,0)
해설: OB-24-1이라는 값을 A16:A19에서 정확한 값을 찾으라는 뜻이고 이렇게 하면 "2"라는 숫자가 나오게 될 것이다.
열에 해당하는 Match 함수
Search_Key는 Date, 즉 A24가 와야한다.
Date가 헤더(제목)에서 몇 번째 있는지 안다면 원하는 Column 값을 우리는 찾을 수 있기 때문이다.
MATCH(A24,$A$16:$E$16,0)
해설: "Date"라는 값을 16번째 행에서 찾아 16번째 행 중 4번째 컬럼에 속한다는 값을 뽑아낸다. 즉 "4"라는 숫자를 나오게 한다.
위 2가지를 합쳐서 수식으로 하면 아래와 같다.
=INDEX($A$16:$E$19,MATCH($B$22,$A$16:$A$19,0),MATCH(A24,$A$16:$E$16,0))
이것은 결국 INDEX(A16:E19,2,4)라는 값과 같은데 2와 4라는 값을 Match라는 함수를 써서 가져오는 것 뿐이다.
결국 INDEX/MATCH 함수 역시 INDEX 함수를 쓰는 것인데 Row와 Column에 들어갈 숫자 값을 MATCH 함수를 활용하여 자동으로 가져오는 것일뿐이기 때문에 어렵지 않음을 확인할 수 있다.
아래 Cost와 PIC 역시 Date에 있는 수식을 복사 및 붙여넣기하게 되면 자동으로 그 값들을 찾아서 가져오는 것을 확인할 수 있을 것이다.
대부분의 함수 기능은 이렇듯, 한개의 함수를 쓰는데 다른 것을 응용해서 자동으로 함수에 값을 넣어준다고 생각하면 편하다.
위와 같이 INDEX/MATCH를 활용하면, VLOOKUP/HLOOKUP 등을 대체하여 사용할 수도 있다. ROW열과 COLUMN 열 어느쪽을 비우느냐에 따라 VLOOKUP이 되기도 하고, HLOOKUP이 되기도 한다. 물론 사실 최근에는 XLOOKUP으로 한번에 처리할 수도 있다.
XLOOKUP은 다음 시간에 따로 한번 다루도록 하겠다.
참고로 위에 달러 표시 등이 많이 있는데, 기본적인 부분이라 여기서는 생략하게 되었다.
엑셀이나 구글스프레드시트를 정말 잘 활용하여 데이터 분석을 하고 싶은 이들을 위해 코멘토(COMENTO.CO.KR)라는 온라인 강의사이트에서 강의를 진행중이다.
현재 기수는 이미 모집이 끝났고, 다음 기수 역시 모집하고 있으니 실습과 함께 엑셀/구글스프레드시트를 활용하여 기본기를 확실히 다지고 싶다면 한번 들어보기를 추천한다.
