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

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

 

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함수에 대한 설명을 마친다. 첨부의 실습자료를 다운로드 받아서 실습해 보자.

 

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

• FORMULATEXT 함수로 수식을 텍스트로 표시하기
• UNIQUE 함수 - 중복 제거하기
• XMATCH 함수 - 범위에서 값의 위치 찾기
• FILTER 함수 - 원하는 조건으로 데이터 조회(필터링)하기
• SORTBY 함수 - 범위의 값을 기준으로 범위의 내용을 정렬하기
• SORT 함수 - 범위 또는 배열의 내용을 정렬하기
• XLOOKUP 함수 - 표에서 값찾기(VLOOKUP 단점 해결)
• HLOOKUP 함수 - 표를 수평으로 따라가면서 값찾기
• HYPERLINK 함수 - 하이퍼링크 만들기
• ROWS 함수 - 범위나 배열의 행의 개수 구하기
• COLUMNS 함수 - 범위나 배열의 열의 개수 구하기
• AREAS 함수 - 참조영역내의 영역의 개수 구하기
• ADDRESS 함수 - 행,열 번호로 셀주소 표시하기
• TRANSPOSE 함수 - 행과 열을 바꾸기
• INDIRECT 함수 – 참조를 동적으로 바꾸기
• LOOKUP 함수 - 행 또는 열의 같은 위치에 있는 값 찾기
• OFFSET 함수 - 행열 이동 후 참조구하기
• COLUMN 함수 - 열번호를 자동으로 매기기
• ROW 함수 - 행번호를 자동으로 매기기
• CHOOSE 함수 - 값목록에서 원하는 값 선택하기
• MATCH 함수 - 특정 범위에서 값의 위치 찾기
• INDEX 함수 - 범위에서 행과 열을 이용하여 값 찾기
• VLOOKUP 함수 - 표에서 값 찾기(간단 버전)
• 값을 찾을 때 VLOOKUP, HLOOKUP 함수 사용하기
• VLOOKUP으로 안될때 INDEX,MATCH 사용하기
• INDEX, MATCH를 이용한 다중조건으로 값찾기