INDEX, MATCH 함수 파헤치기 #1

INDEX, MATCH 함수 파헤치기 #1

 

이번 포스팅에서는 INDEX와 MATCH 함수를 배워보도록 하겠습니다.

 

두 함수 모두 독립적으로도 활용이 가능하나 VLOOKUP 함수의 한계(매칭하는 대상의 값이 항상 테이블(범위)의 좌측에 있어야 됨)로 인해 INDEX와 MATCH 함수를 함께 적용하여 VLOOKUP 함수 대신 사용하는 경우가 많습니다.

 

사용법만 잘 익힌다면 오히려 VLOOKUP 함수보다도 편리하므로 함께 익혀보도록 하겠습니다.

 

우선 각 함수에 대한 내용을 살펴본 후 두 함수를 함께 적용하여 문제를 푸는 방법을 알아보도록 하겠습니다.

 

 

1. INDEX 함수

 

INDEX 함수의 구조를 보면 INDEX(값을 찾을 범위,행 번호, 열 번호) 형태입니다.

INDEX 함수 마법사

함수 안에 첫 번째 인수로 지정된 범위를 '표'로 인식하고 그 표의 좌측 상단 시작점을 기준으로 각각 두 번째, 세 번째 인수로 입력한 숫자에 따라 아래쪽(행)과 오른쪽(열)으로 이동하는 이동 하여 해당 위치에 있는 데이터 값을 반환하는 함수입니다.

INDEX 함수 이해

위의 표에서 노란색으로 되어 있는 범위의 좌측 상단 끝 시작점이 '1행 1열'이며, 우측으로 이동 시 '열' 번호가 변경되고 아래로 이동 시 '행' 번호가 변경되어 우측 하단의 값은 '5행 5열'인 것을 확인할 수 있습니다.

 

이렇게 범위에서 추출할 행과 열 번호를 INDEX 함수를 통해 지정하면 원하는 값을 반환할 수 있습니다.

 

자세한 내용은 예제를 통해 살표 보도록 하겠습니다.

 

 

2. INDEX 함수 예제(1)

INDEX 함수 예제1

제시된 표에서 3, 5, 7번째 행에 있는 값을 구하는 예제입니다.

 

INDEX 함수를 적용하여 풀어보면, INDEX(데이터 범위,3,1)로 수식을 작성할 수 있습니다.

 

여기서 데이터 범위는 데이터가 입력된 범위만 잡게 되며, 이 경우 한 개의 열만 존재하므로 마지막 인수인 '1'은 생략해도 됩니다.

 

결과식을 모두 풀어보면 아래와 같습니다.

예제1 풀이

3, 5번째 행의 값은 각각 B3~B7의 셀 중 3, 5번째 값이므로 '다1'과 '마1'로 반환되는 것을 확인할 수 있습니다.

하지만 마지막 조건은 B3부터 B7까지 5개의 행만 존재하는데 7번째 행의 값을 반환하도록 하였으므로 에러 값이 나오게 됩니다.

 

 

3. INDEX 함수 예제(2)

INDEX 함수 예제2

2번 예제는 1번과 행, 열이 변경된 문제입니다.

 

해당 문제는 아래와 같이 풀 수 있습니다.

예제2 풀이

표에서 2, 4, 6번째 열의 값을 구하는 문제는 위와 같이 수식을 정리할 수 있으며, 주어진 범위의 행이 한 개이므로 행 번호를 생략하여 B6셀에 INDEX($B$3:$F$3,,2)와 같이 작성할 수도 있습니다.

 

 

4. INDEX 함수 예제(3)

 

INDEX 함수 마지막 예제는 다음과 같습니다.

INDEX 함수 예제3

행과 열 모두 다섯 가지인 표에서 주어진 조건에 맞는 셀의 값을 찾는 문제입니다.

 

INDEX 함수의 인수로 범위를 지정한 후 각각 행과 열 번호만 입력해주면 완성됩니다.

예제3 풀이

 

이어서 MATCH 함수에 대해 설명하도록 하겠습니다.

 

 

5. MATCH 함수 

 

MATCH 함수의 구조는 MATCH(찾으려는 값,비교할 범위,정확도 옵션)과 같습니다.

 

여기서 찾으려는 값은 비교할 범위에서 찾고자 하는 조건 값이 되며, 비교할 범위에서 해당하는 값을 찾아 몇 번째 위치에 있는지 값을 반환하게 됩니다.

마지막으로 정확도 옵션은 찾고자 하는 값이 정확히 일치해야 될 경우 '0', 찾으려는 값 보다 작거나 같은 값 중 가장 큰 값을 반환하려면 '1', 찾으려는 값 보다 크거나 같은 값 중 가장 작은 값을 반환하려면 '-1'을 입력합니다.

 

보통 찾으려는 값과 정확히 일치하는 값을 찾는 경우가 많으므로 실제 '1'과 '-1'은 사용할 일이 많지 않으니 '0'을 기본 옵션으로 생각하고 문제를 풀도록 하겠습니다.

 

 

6. MATCH 함수 예제

예제

표의 행과 열 구분 값이 각각 범위에서 몇 번째 행 또는 열인지 해당하는 번호를 반환하는 문제입니다.

 

첫 번째 조건 문제를 풀어보면 MATCH("다",'가~마'가 입력된 범위,0)와 같이 수식을 작성할 수 있으며, 두 번째 조건 문제는 MATCH("BB",'AA~EE'가 입력된 범위,0)로 정리할 수 있습니다.

 

이를 풀어보면 아래와 같이 나옵니다.

예제 풀이

'가~마'가 입력된 A3~A7셀에서 '다'는 3번째 행에 위치해 있으므로 결과는 '3'이 나오고 동일한 이유로 'BB'에 대한 열 번호는 '2'라는 결과가 나오게 됩니다.

 

※ 수식의 첫 번째 인수인 찾으려는 기준 값은 위와 같이 특정 문자를 입력할 수도 있지만 셀의 값으로 지정하거나 인수 자체에 수식을 작성하여 이를 통해 반환된 값도 사용할 수 있습니다.

 

다음 포스팅에서는 INDEX와 MATCH 함수를 함께 응용하는 방법을 알아보도록 하겠습니다.