MATCH, INDEX 함수

전체 목차

 - 001. SUMIF 함수, SUMIFS 함수, COUNTIF 함수, COUNTIFS 함수

 

포스트 목차.

01. MATCH 함수

02. INDEX 함수

 

본 포스트에서는 Lookup과 유사한 작업을 할 수 있는 함수들을 알아보자.

 

01. MATCH 함수

 

MATCH 함수는 배열에서 찾고자 하는 값의 상대 위치를 반환하는 역할을 수행한다.

 

예를 들어, {A, B, C, D, E, F, G} 가 들어있는 배열에서 C를 찾는다고 하면, C의 상대위치인 3이 반환된다.

 

MATCH 함수의 인수(파라미터)는 다음과 같다.

 

 - Lookup_value: 찾을 값을 의미한다.

 - Lookup_array: 찾을 값이 저장되어 있는 배열을 의미한다.

 - Match_type: 찾는 연산의 종류를 지정할 수 있다.

   (1: 보다 작음, 0: 일치, -1: 보다 큼)

 

예제를 확인하며, MATCH 함수에 익숙해지도록 하자.

 

(1)  Match_type이 0인 경우

 

다음과 같은 데이터가 있을 때 MATCH 함수를 사용하여 꼬마성어린이집의 위치를 찾아보자. (머리글행은 빼고 데이터부터 카운트해보자)

 

*참고로 아래의 데이터는 오픈데이터 포탈에서 얻을 수 있는 울산광역시 미세먼지 측정 대상 업체를 나타낸다.

다음과 같이 MATCH 함수를 작성해보자

 

=MATCH(I2, $A$2:$A$51, 0) 

 - lookup_value: I2(꼬마성어린이집)

 - lookup_array: A2:A51(업소명 열)

 - match_type: 0 (일치)

 

두번째 인자로 지정한 영역에서 꼬마성어린이집의 위치는 5번째이므로 5가 출력될 것으로 예상된다.

5가 출력되는 것을 확인할 수 있다.

 

(2) match_type이 1인 경우

lookup_array가 오름차순으로 정렬되어 있어야 한다.

lookup_value 보다 작거나 같은 값 중 가장 큰 값의 위치를 반환한다.

 

다음 예제를 보면 B열이 오름차순으로 정렬되어 있다.

lookup_value인 16보다 작거나 같은 값 중 가장 큰값인 15의 위치 2를 반환한다.

(3) match_type이 -1인 경우

lookup_array가 내림차순으로 정렬되어 있어야 한다.

lookup_value 보다 크거나 같은 값 중 가장 작은 값의 위치를 반환한다.

 

다음 예제를 보면 G열이 내림차순으로 정렬되어 있다.

lookup_value인 16보다 크거나 같은 값 중 가장 작은 값인 18의 위치 5를 반환한다.

 

 

MATCH 함수를 언제 사용하면 좋을까? 라는 의문이 들 것이다. 다음 MATCH 함수와 세트로 사용될 수 있는 INDEX 함수에 대해 알아보고 MATCH 함수의 활용도에 대해 논의 해보자.

 

02. INDEX 함수

 

INDEX 함수는 배열에서 특정 위치(상대위치)에 있는 값을 반환하는 역할을 수행한다.

 

예를 들어, {A, B, C, D, E, F, G} 가 들어있는 배열에서 상대위치 3을 입력하면, C가 반환된다.

 

INDEX 함수는 다음과 같이 두 가지 형태의 파마리터를 사용할 수 있다. 

 

(1) array, row_num, column_num

 - Array: 데이터가 있는 영역

 - Row_num: 줄번호

 - Column_num: 열번호

 

다음예제를 살펴보자

=INDEX(C2:C51, I3)

 - Array: 소재지 데이터가 있는 C2:C51 영역

 - Row_num: 5를 입력

 - Column_num: 생략

 

다섯번째 데이터, 즉 꼬마서 어린이집의 소재지가 반환될 것으로 예상된다.

 

이번에는 2차원 배열을 지정해보자.

=INDEX(A2:C13, 3, 2)

 - Array: A2:C13의 데이터 영역

 - Row_num: 3을 입력

 - Column_num: 2를 입력

 

3번째 행, 2번째 열의 데이터인 052-233-6100이 반환될 것으로 예상된다.

 

 

(2) reference, row_num, column_num, area_num

 

INDEX함수는 파라미터를 4개를 가질 수도 있다.

 

 - Reference: 하나 또는 이상의 영역

 - Row_num: 찾으려고하는 행의 위치(상대위치)

 - Column_num: 찾으려고하는 열의 위치(상대위치) 

 - Area_num: Reference에 포함되어 있는 영역이 2개 이상일때 , 몇번째 영역에서의 Row_num, Column_num에 해당하는 데이터를 찾아야할 지를 지정한다.

 

다음 예를 살펴보자

 

=INDEX((A2:F14,A21:F35),3,2,1)

 - Reference: A2:F14, A21:F35 의 두 영역 입력

 - Row_num: 3번째 행

 - Column_num: 2번재 열

 - Area_num: 첫번째 영역(A2:F14) 

 

첫번째 영역(A2:F14)의 3번째 행, 2번째 열에 해당하는 052-233-6100이 나온다.

Area_num을 2로 바꾸면, 두번째 영역(A21:F35)의 3번째 행, 2번째 열에 해당하는 052-252-0606이 나온다.

 

03. MATCH 함수와 INDEX 함수의 활용

 

두 함수를 조합하면 Lookup과 유사한 효과를 낼 수 있다.

 

예를 들어 아래의 표에서 선재어린이집의 소재지 연락처를 검색하는 함수를 만들어보자

 

=INDEX(C2:C23, MATCH(I9, A2:A23,0))

 

일단 안에 있는 MATCH함수는 A2:A23의 영역에서 I9(선재어린이집)이 몇번째에 있는지를 반환한다.  21번째에 있다.

=INDEX(C2:C23, 21)

INDEX 함수는 C2:C23에서 21번째 값을 찾는다. 

즉, 선재어린이집의 소재지인 "울산광역시 동구 화장6길 63 (방어동)"이 반환된다.

 

이런식으로 함께 활용하여 찾는 데이터의 다른 필드에 해당하는 데이터를 가져오는 함수를 만들 수 있다.