INDEX, MATCH 함수 파헤치기 #2

INDEX, MATCH 함수 파헤치기 #2

 

이번에는 INDEX 함수와 MATCH 함수를 함께 사용하는 방법을 알아보려고 합니다.

 

INDEX, MATCH 함수는 VLOOKUP 함수처럼 기준이 되는 조건을 매칭 시켜 그에 대응되는 항목의 값을 불러오는 목적으로 사용을 하게 됩니다.

 

VLOOKUP 함수는 매칭 하는 기준이 테이블의 가장 좌측에 있어야 된다는 부분 때문에 사용에 제약이 있으나 INDEX, MATCH 함수는 이러한 제약 없이 사용이 가능하다는 장점이 있습니다.

 

주로 많이 쓰이는 상황을 가정하여 예시를 통해 설명하도록 하겠습니다.

 

 

1. INDEX, MATCH 함수 예제(1)

예제1

주어진 표에서 고유번호에 따른 고객의 정보를 불러오는 예제입니다.

 

※ VLOOKUP 함수와의 비교를 위해 동일한 데이터(고유번호의 위치는 좌측 첫 번째 열에서 중간으로 이동)를 예제로 사용하였습니다.

 

해당 문제를 VLOOKUP 함수로 풀기 위해서는 '이름'이 지정된 열 좌측에 추가로 열을 삽입한 후 '고유번호'를 붙여 넣어 좌측에 새로운 열을 만든 후 작업하거나 LOOKUP 함수를 이용하여 다소 복잡하게 해결해야 되는 상황이 발생하게 됩니다.

 

하지만 INDEX, MATCH 함수를 통해 좀 더 쉽게 해결하는 방법을 알려드리도록 하겠습니다.

 

우선 수식은 INDEX(추출하려는 범위,MATCH(고유번호 셀,고유 번호가 포함된 범위,0))의 형태로 작성할 수 있으며, MATCH 함수를 통해 찾고자 하는 고유번호가 해당 범위에서 몇 번째 행에 위치해 있는지 확인한 후 해당하는 위치에 있는 값을 INDEX 함수의 첫 번째 인수로 지정한 범위에서 추출하게 됩니다.

 

이런 내용을 적용하여 문제를 풀어보면 다음과 같습니다.

예제1 풀이

우선 G열의 '고유번호'에 따른 '이름'을 구하기 위해 INDEX 함수의 첫 번째 인수인 추출하려는 범위에는 A열(A3~A27)의 범위가 인수로 들어가며, MATCH 함수를 통해 G3셀 값과 C열(C3~C27)의 범위 값을 비교하여 G3셀에 해당하는 고유번호 'A881273'이 몇 번째 행에 위치해 있는지 찾습니다.

해당 고유 번호는 좌측 표의 5번 행(C7셀)에 위치해 있는 것을 확인할 수 있으며, 이에 따라 추출하려는 값이 지정된 A3~A27 범위에서 5번 행(A7셀)의 값인 '양*영'을 반환하게 됩니다.

 

동일한 방법으로 INDEX 함수의 추출하려는 범위만 '구매일', '물품번호', '전화번호'에 맞게 각각 'B', 'D', 'E' 열의 범위로 지정해주면 수식이 완성됩니다.

 

※ 매칭 하는 대상인 고유 번호와 고유 번호가 입력된 표의 범위는 계속 비교해야 되므로 MATCH 함수의 수식은 변하지 않습니다.

 

※ INDEX, MATCH 함수 사용 시 유의해야 될 사항으로 해당 함수의 결과 산출 방식은 MATCH 함수를 통해 찾아낸 결과의 '위치' 값을 적용하여 그대로 INDEX 함수에서 지정한 추출하려는 범위에 해당 위치 값에 맞는 데이터를 추출하는 것입니다.

따라서 INDEX 함수에서 지정한 범위와 MATCH 함수에서 기준에 맞는 위치를 찾기 위해 지정한 범위의 시작점은 동일해야 됩니다.

 

만약 위의 예제에서 MATCH 함수의 매칭 범위 시작점이 C3셀이 아닌 C4셀이라면? 결과는 아래와 같이 잘못된 값을 반환하게 됩니다.

잘못 된 범위 설정

고유 번호는 위의 예제와 변함이 없으므로 H3셀에 이름은 분명 '양*영'이 반환되어야 되는데 '박*정'이 반환되었습니다.

이유는 고유번호 'A881273(C7셀)'은 'C4'셀을 시작점으로 하는 MATCH 함수의 범위로 보면 'C4', 'C5', 'C6', 'C7'처럼 4번 행에 위치한 것으로 MATCH 함수의 결과를 반환하게 됩니다.

여기서 INDEX 함수의 범위는 'A3'셀부터 시작하므로 범위의 4번 행에 위치한 셀은 'A3', 'A4', 'A5', 'A6'이며, 이에 따라 'A6'셀의 값인 '박*정'을 최종 결과 값으로 반환하게 되는 것입니다.

 

 

2. INDEX, MATCH 함수 예제(2)

예제2

이번 예제는 테이블에 각각 '번호'와 '순서'에 따라 이에 해당하는 값이 주어져 있으며, 이때 주어진 '번호'와 '순서'의 조건에 맞는 테이블 안의 값을 구하는 문제입니다.

 

이 문제를 풀기 위해서 INDEX 함수의 추출 범위로 값이 표시된 테이블을 지정하고 INDEX 함수의 2, 3번째 인수인 행 번호와 열 번호 부분에 각각 MATCH 함수를 통해 주어진 조건에 맞는 값이 몇 행인지, 몇 열인지를 찾아 추출 범위에서 해당 값이 반환될 수 있도록 수식을 작성해야 됩니다.

 

이를 풀어보면 INDEX(추출하려는 범위,MATCH(번호 조건,번호 조건 포함 범위,0),MATCH(순서 조건, 순서 조건 포함 범위,0))과 같이 수식을 작성할 수 있습니다.

 

※ INDEX 함수의 2번 인수는 '행' 번호이므로 표에서 행을 나타내는 '번호' 조건이 들어가야 되며, 3번 인수는 '열' 번호이므로 표에서 열을 나타내는 '순서' 조건이 들어가야 됩니다.

 

위 수식을 적용하여 다음과 같이 문제를 풀 수 있습니다.

예제2 풀이

B12셀 풀이를 살펴보면, 'C4'부터 'G8'셀까지 추출 범위를 지정하고 번호 '다'가 몇 행인지 확인하기 위해 MATCH 함수로 B10 셀의 '다'와 B4~B8 범위를 비교합니다.

이후 순서 'C'가 몇 열인지 확인하기 위해 MATCH 함수로 B11 셀의 'C'와 C3~G3 범위를 비교합니다.

 

해당 수식을 통해 표의 범위에서 '다'는 3행, 'C'는 3 열이라는 것을 확인할 수 있으며, 표에서 3행, 3열에 있는 값인 '다57489C'를 결과 값으로 반환하게 됩니다.

 

함수를 처음 접하고 여러 함수를 함께 사용하는 수식을 작성하게 되면 이해하는데 다소 시간이 걸릴 수 있으나, 계속 반복하여 학습하다 보면 조금씩 익숙해지게 됩니다.

 

저의 포스팅이 함수를 이해하고 수식을 작성하는데 더 익숙해질 수 있는 기회가 되었으면 합니다.