엑셀함수 INDEX - 특정 범위에서 행과 열을 이용하여 값 찾기

엑셀의 INDEX함수는 테이블이나 범위에서 값 또는 값에 대한 참조를 반환한다. INDEX 함수에는 배열형과 참조형이라는 두 가지 형식이 있다.
이 함수는 단독으로 쓰이기 보다는 MATCH함수 등 다른 함수와 같이 자주 사용된다. INDEX와 MATCH함수를 같이 사용하면 VLOOKUP으로 해결할 수 없는 다중 조건으로 값찾기 등의 문제를 해결할 수 있다. INDEX,MATCH를 이용한 다중조건 값찾기는 "INDEX, MATCH를 이용한 다중조건으로 값찾기" 를 참고하자.

구문(Syntax)

(배열형) INDEX(array, row_num, [column_num])

테이블 또는 배열에서 행과 열에 해당하는 값을 가져온다.

인수 :

array : 값을 찾을 테이블 또는 배열
row_num : 값이 위치한 행번호
column_num : (생략가능)값이 위치한 열번호

(참조형) INDEX(reference, row_num, [column_num], [area_num])

참조영역에서 행과 열에 해당하는 셀 참조를 가져온다.

인수 :

reference : 값을 찾을 참조영역
row_num : 값이 위치한 행번호
column_num : (생략가능)값이 위치한 열번호
area_num : (생략가능)값이 위치한 영역의 번호, 생략시 1이 기본값으로 사용됨

1. 배열형 사용예 :

1) 특정 영역에서 행과 열에 해당하는 값을 가져오기

아래의 판매실적 자료에서 5행, 3열에 해당하는 값을 가져오기 위해서 C2셀에 =INDEX(B5:D11,5,3)을 입력해보자.

첫번째 인수 B5:D11은 값을 찾을 영역이다.
두번째 인수 5는 이 영역에서 5번째 행을 말하고
세번째 인수 3은 3번째 열을 말한다.

수식이 정상적으로 입력되었으면 C2셀에 5월 자영대리점의 판매실적인 67,843,000원이 표시될 것이다.

2) 행이나 열 전체를 배열형태로 가져오기

Index함수에서 두번째/세번째 인수인 행 또는 열을 생략하거나 0을 입력하면 배열을 반환한다. 아래의 판매실적 자료에서 5행전체를 배열형태로 가져오기 위해서 F5셀에 =INDEX(C5:D11,5,0)을 입력후 바로 Enter를 누르지 말고 Ctrl + Shift + Enter를 입력해보자.

첫번째 인수 B5:D11은 값을 찾을 영역이다.
두번째 인수 5는 이 영역에서 5번째 행을 말하고
세번째 인수 0은 특정열을 지정하지 않는 것을 의미한다. 즉 전체열을 의미한다. 0을 입력하지 않고
=INDEX(C5:D11,5,)처럼 인수값을 비워도 동일한 수식이다.

배열수식을 입력하면 수식 앞뒤에 중괄호{}가 붙는다. 중괄호{}는 입력한 값이 아니다. 수식을 입력하고 Ctrl +Shift + Enter를 입력하면 자동으로 생긴다.

배열수식을 입력시 Ctrl +Shift + Enter를 입력하지 않고 Enter만 입력하면 아래와 같이 오류가 난다.

그런데 배열수식을 입력했는데 보이는 값은 배열이 아니고 배열의 첫번째 값인 53,789,000원만 보인다. 배열값을 보기 위해서는 좀 특별한 방법을 이용해야 한다.

배열이 입력된 수식을 마우스로 선택하고 F9(펑션키)를 입력하면 아래와 같이 수식입력줄에 ={53789000,67843000}의 형태로 배열이 표시된다.

배열수식의 배열확인하기

배열수식은 그 자체로 이용되기 보다는 SUM, COUNT등의 함수로 연산하기 위해서 많이 사용된다. 아래와 같이 =SUM(INDEX(C5:D11,5,0))을 입력하면 반환된 배열을 더해준다.

그런데, 여기서 드는 의문 하나... 배열수식을 입력하고 배열수식에 SUM까지 입력했는데 그냥 5행을 선택하고 SUM함수 입력한 거랑 똑 같지 않은가?

그냥 SUM함수를 입력하면 될 걸... 왜 이렇게 복잡한 것을 할까하는 의문이 들것이다.

예시와 같이 단순한 경우에는 SUM함수를 쓰는 것과 동일하겠지만 좀 더 복잡한 경우에는 SUM함수만으로 해결이 안되는 경우가 있다. 예를 들면 SUM을 하는 행과 열이 동적으로 바뀌는 경우에 SUM함수만으로는 안되고 INDEX함수에서 행과 열번호만 바뀌서 SUM하면 쉽게 해결할 수 있다.

말로는 설명이 어렵지만 이 부분은 INDEX함수 응용편에서 자세히 다루기로 한다.

2. 참조형 사용예 :

1) 여러 영역에서 행과 열에 해당하는 값을 가져오기

참조형 INDEX함수는 지역별 판매실적처럼 여러 영역이 있고 특정 영역에서 행과 열에 해당하는 값을 가져올 때 사용할 수 있다.

아래의 판매실적 자료에서

동대문구에 있는 판매 대리점 중
직영대리점의
5월 판매실적을 가져오기 위해

c2셀에 =INDEX((B6:D12,F6:H12,B16:D22,F16:H22),5,2,4)을 입력해보자.

첫번째 인수 (B6:D12,F6:H12,B16:D22,F16:H22)는 값을 찾을 영역이다. 아래 4개의 지역을 말한다. B6:D12는 첫번째 지역인 종로구를 말하고, ,F6:H12는 두번째 지역인 강남구를 의미한다. 세번째, 네번째도 같은 방식으로 입력되어 있다.
두번째 인수 5는 영역에서 5번째 행을 말하고
세번째 인수 2는 2번째 열을 말한다.
네번째 인수 4는 첫번째 인수로 입력한 영역에서 4번째를 말한다. 즉 동대문구를 의미한다.

수식이 정상적으로 입력되었으면 C2셀에 판매실적인 64,549,000원이 표시된다.

첨부(실습용 엑셀 파일)

엑셀함수 INDEX와 MATCH – 범위에서 값과 위치 찾기.xlsx

이것으로 INDEX함수에 대한 설명을 마친다. 첨부의 실습자료를 다운로드 받아서 실습해 보자.

[ 엑셀 찾기 및 참조영역 함수 목록 ]