[엑셀] INDEX 함수

찾기/참조 영역 함수는 VLOOKUP 함수나 INDEX함수, OFFSET 함수는 서로 닮은 듯 닮지 않은 함수입니다.

INDEX 함수는 배열형과 참조형 2가지가 있으며 배열형의 경우는 VLOOKUP 함수와 아주 많이 닮았습니다.

사용의 편의성을 봐서는 VLOOKUP 함수가 더 쉽게 느껴지는데 이유는 lookup_value의 조건에 맞는 행을 알아서 찾아 주기 때문이겠죠!!

그렇지만 INDEX 함수는 배열수식으로 활용이 쉬워 배열수식으로 많이 사용하는 함수이기도 합니다.

오늘은 INDEX 함수의 기본 이해와 MATCH 함수와 응용 정도로 알아보고 다음에 INDEX 함수와 MATCH함수의 복합함수를 사용자 정의 함수로 만들어 보겠습니다.

함수마법사에서 INDEX 함수를 사용하려면 배열형 또는 참조형 중에 하나를 선택해야 합니다.

배열형 INDEX 함수

행과 열 번호 인덱스로 선택한 배열이나 표 요소의 값을 반환

수식 = INDEX(array, row_num, [column_num])

☞ 배열형 INDEX 함수의 인수

1. array : 배열 상수나 셀 범위

2. row_num :값을 반환할 배열의 행

3. column_num : 값을 반환할 배열의 열

※ 주의

1. row_num이나 column_num을 0으로 설정하면 수식이 있는 열이나 행에 대한 값의 배열이 각각 반환

2. row_num과 column_num은 반드시 배열에 있는 셀이어야 함.

☞ 수식설명

1번 : row_num에 4, col_num에 5의 값(21.7)을 반환

2번 : col_num에 0을 넣으면 결과(수식이 있는 E열)의 열에서 아래로 4번째 값(17.3)을 반환

3번 : row_num에 1, col_num에 2의 값(31.6)을 반환

4번 : 2번과 반대로 row_num에 0을 넣으면 결과(수식이 있는 23행)의 행에서 우측으로 2번째 값을 반환해야 되는데 영역범위 밖이라 #VALUE! 에러가 발생

5번 : col_num을 생략 하면 #REF! 에러 발생

INDEX 함수 + MATCH 함수

MATCH 함수는 찾기함수에서 약방에 감초 같은 함수 입니다. 안끼는데가 없죠??

INDEX 함수와 MATCH 함수로 복합함수를 만들어 사용하면 row_num과 col_num에 숫자를 넣지 않고 조건을 걸어 원하는 값을 찾을 수 있습니다.

※ 이때 주의 해야 할 것은 MATCH 함수의 참조열과 행이 오름차순으로 정렬이 되어있어야 합니다.

1번 : 조건1의 83%는 MATCH 함수 결과 4가 되며, 조건2의 2008은 MATCH 함수 결과 2가 되어 결과 17.3을 반환

2번, 3번 : 1번과 동일 하며 눈으로 한번 지점을 찾아 크로스 되는 위치의 값이 일치 하는지 확인 바람

참조형 INDEX 함수(다중영역 참조)

특정 행과 열이 교차되는 위치의 셀 참조를 반환. 참조가 2개의 영역으로 구분된 경우 영역 선택 가능

수식 = INDEX(reference, row_num, [column_num], [area_num])

☞ 참조형 INDEX 함수의 인수

1. reference : 하나 이상의 셀 범위를 포함하는 참조 영역(2개 이상의 영역을 참조로 입력하려면 참조를 괄호로 묶어야 함)

2. row_num : 참조 범위에서 참조를 반환할 행 번호

3. column_num : 참조 범위에서 참조를 반환할 열 번호

4. area_num : 2개 이상의 영역 중 원하는 영역 번호

※ 주의

1. row_num이나 column_num을 0으로 설정하면 수식이 있는 열이나 행에 대한 값의 배열이 각각 반환

2. row_num, column_num, area_num은 반드시 reference 안의 셀이어야 함

3. INDEX 함수의 반환 값은 수식에 따라 참조로 사용될 수도 있고 값으로 사용될 수도 있음

INDEX 함수는 위 그림처럼 1개 이상의 다중영역에서 데이터를 찾을 수 있습니다.

물론 수식의 마지막에 영역을 선택해야 하며, VLOOKUP 함수나 OFFSET 함수에서는 흉내도 내지 못하는 기능입니다. ^^

☞ 데이터를 찾는 원리는 배열형과 같으며 여러 영역 범위를 참조 한다는거.. 그리고 여러 참조 영역은 ( 괄호 )로 묶어야 한다는것만 기억하세요. 수식설명은 따로 하지 않겠습니다.

※ INDEX 함수의 반환값이 수식에 따라 참조가 될 수 있고 값이 될 수 있다고 하는데(주의3번 참조) 간단히 설명을 드리면 위 1번 수식의 결과가 21.7이며 H8 셀에 위치합니다. 위 수식에서 결과 값은 21.7이 나오지만 =row(INDEX(($D$5:$L$9,$D$10:$L$17),C33,D33,E33))=row(H8)이 되어 결과는 8이 나옵니다. 그래서 참조가 될 수 있다는 거죠!

관련글 보기

VLOOKUP 함수