INDEX/MATCH 활용[엑셀/구글스프레드시트]

자동으로 값을 매칭시켜 값을 불러와보자

Google SpreadSheet 데이터 분석 강의를 하며 쿼리를 집중적으로 가르치지만, 부분적으로 다른 함수도 설명해주고 있다. 그 중에서 가장 유용한 함수를 하나 꼽자면 MATCH 함수를 꼽을 수 있다.

MATCH 함수는 홀로 쓰일 때는 그 빛을 발휘하지 못하지만, INDEX 함수나 다른 함수와 같이 쓰일 경우 번거로운 작업을 크게 줄여줄 수 있는 매력적인 함수이다.

Index 함수와 Match 함수를 조합하여 Vlookup 혹은 Xlookup 효과를 낼 수 있다. Lookup함수는 조건에 맞는 매핑 데이터를 가져오는 함수라고 생각하면 쉽다.

(아직도 Vlookup을 많이 쓰는 것 같은데, Xlookup은 약 2020년쯤 새로 나온 함수로 훨씬 더 쉬운데 아직 잘 안 알려진 것 같다. 추후 Xlookup도 다뤄보도록 하겠다.)

1. Match 함수

우선 아래에서 MATCH 함수에 대한 설명과 예시를 살펴보도록 하자.

1. MATCH 함수 설명

Syntax

- 특정 범위(Range)에서 특정 값(Search_key)이 매칭되는 위치를 출력한다.

EX)MATCH("Sunday",A2:A9,0)

MATCH(search_key, range, [search_type])

1. search_key - 찾아야 할 값. 예시, 42, "Cats", or I24.

2. range - search key 가 속한 범위를 정해주면 된다. 범위는 1보다 무조건 커야하며, 그렇지 않으면 N/A 출력.

3. search_type - [ OPTIONAL - 1 by default ] - The manner in which to search.

-1, 기본값, 범위를 오름차순으로 정렬한 후 search_key 이하 중 가장 큰 값을 출력.

-0, 정확한 값을 찾으며, 범위가 정렬되지 않음

-1 범위를 내림차순으로 정렬한 후 search_key 이상의 값 중 가장 작은 값을 출력

MATCH 함수는 특정 범위에서 찾아야 하는 값이 몇 번째 있는지를 출력하는 함수이다.

간단히 설명하면 하나의 값(Search Key)이 정해진 범위(Range)에서 몇 번째에 있는지 찾는 함수다.

아래 예시를 보며 확인해보자.

아래 범위를 보면 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 값이 입력되면 내가 원한 값이 아닌 가장 근사한 값을 매칭해 불러올 수 있다는 것을 꼭 유념하도록 하자.

2. Index 함수

그럼 MATCH 함수와 궁합이 잘 맞는 INDEX 함수에 대해서도 알아보자.

2. INDEX 함수 설명

Syntax

행과 열을 인식하여, 범위에서 행과 열에 속하는 값을 출력한다.

EX) INDEX(A1:C20, 5, 1)

INDEX(reference, [row], [column])

1. reference - 값이 불러져오는 셀의 범위.

2. row - [OPTIONAL - 0 by default] - reference 범위에서 불러져오는 행 순서.

3. column - [OPTIONAL - 0 by default] - reference 범위에서 불러져오는 열 순서.

인덱스 함수는 벡터범위에서 그에 해당하는 행과 열에 해당하는 값을 가져오는 함수이다.

예시를 위해 아래 데이터를 보도록 하자.

위 데이터는 영어에 해당하는 한글의 값을 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열에 속하는 "나"라는 값을 출력하게 되는 것이다.

참고로 행과 열 둘 중 하나만 와도 상관은 없다는 점을 기억하고 Index 함수와 Match 함수를 조합해보자.

3. Index/Match 조합

그럼 두 개를 조합해보도록 하자.

사실 여기서 명심할 것은 결국 Index 함수를 쓴다는 것을 꼭 알아두도록 하자.

아래 J열과 K열의 도로명과 동을 보도록 하자.

도로명에 해당하는 동을 가져오려면 어떻게 할까?

아래는 Index, Match 함수이다.

Index 함수

1. reference - 범위를 설정한다. 내가 불러올 범위를 설정한다. 지금 동을 불러올거니, K열 선택.

2. row - 여기서 몇 번째 있는 것을 불러올지 쓰면 된다. 이것을 Match 함수로 숫자를 불러오는 것이다.

3. column - 생략

Match 함수

1. search_key - 내가 찾을 값. 텍스트 혹은 셀 주소 입력. 여기서는 '자하문로26길'

2. range -자하문로26길이 포함된 범위인 J열을 선택하자

3. search_type - 무조건 0을 써주면 된다.

너무 쉽지 않은가?

결국 Index 함수를 쓰는건데, Match 함수를 이용해서 Row 값을 자동으로 넣는거다.

이런식으로 Index, Match 함수를 조합하여 Lookup 함수를 대체할 수 있다.

자주 사용해보면 좋으나, 추후 Xlookup에 대해 알려줄테니 그걸 사용하면 더 쉬울거다.