Home > 엑셀 함수 > 찾기 및 참조영역 함수 > 엑셀함수 INDEX – 특정 범위에서 행과 열을 이용하여 값 찾기

엑셀함수 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만 입력하면 아래와 같이 오류가 난다.
Ctrl + Shift + Enter를 입력하지 않으면 오류가 발생한다

 

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

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

배열수식의 배열확인하기

배열수식의 배열확인하기

배열값 확인하기

 

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

 

SUM함수로 배열수식을 더한 결과

 

그런데, 여기서 드는 의문 하나... 배열수식을 입력하고 배열수식에 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번째를 말한다. 즉 동대문구를 의미한다.

참조형 INDEX함수

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

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

 

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

“엑셀함수 INDEX - 특정 범위에서 행과 열을 이용하여 값 찾기”의 6개의 댓글

  1. 엑셀강의 잘보고있습니다. 혹시 제 문제좀 해결해주실수있나요?

    LEFT RIGHT MID로 그 품목에관한 번호를 추출하여 다시 합칠때 그 품복에 관한 이름을 다시 표현하고싶은데 힘드네용 ㅠㅠ 예를들어 123456이라는 번호가있으면 12는 품목명이고 34가 색깔 56이 품목사이즈면 12 34 56을 추출하여 그 이름에 맞는 결과값을 문자로 볼수있으면한데 ㅠㅠ 예를들어 구두(12)파랑색 (34)LARGE(56)면 바코드로 찍으면 숫자만 123456이 나와서 그걸 바로 문자로 보게끔 구두파랑색LARGE이렇게 보고싶게만들고싶어요

    간단히말해서 바코드를 이용한 엑셀활용인데 바코드수는 6자리이고 그 6자리안에 물건에대한 정보를 엑셀에 숫자로 입력하여 그 숫자를 다시 VLOOKUP이나 INDEX를 활용하여 물건이름이 나오게끔 하고싶어용

    1. 안녕하세요. 각각의 자리수 마다 물건의 정보를 가지고 오려면 별도의 시트에 코드값과 코드값에 해당하는 이름정보를 관리하고 VLOOKUP이나 INDEX/MATCH함수를 이용해서 보여주면 되지 않을까합니다.
      별도의 시트는 다음과 같이 구성하면 될듯합니다.

      품목코드,품목명,색깔코드,색깔명,품목사이즈코드,사이즈
      ------------------------------------------------------------------------------
      12,구두,34,파랑,56,LARGE
      13,숄더백,35,빨강,57,MIDDLE
      14,서류가방,36,갈색,58,SMALL
      감사합니다.

  2. 고수님들 질문이 있습니다.
    저는 건설일에 뛰어든 병아리 입니다.
    요즘 내역서와 씨름중인데요..ㅠㅠ
    거래처에서 보낸서류를 변경하여 협의된 금액으로 변경해야하는데 넘 힘들어서요..
    예시)수식이 포함된 전체값(견적금액의 81%값)합계가
    123,456,789입니다.
    거래처 계약금은 123,000,000 입니다.이금액을 마춰야합니다.
    전체 문서를 손대서 이금액을 마추려니 일일이 이윤(%)에서 소수점작업해서 마추고 있습니다.
    한번에 값을 계산할수 없을까요?
    부끄럽지만 부탁드립니다.

    1. 안녕하세요. 작업하고 계신 파일을 보아야 도움을 드릴 수 있을 것 같습니다. 괜찮으시다면 admin@xlworks.net으로 보내주시면 확인해보겠습니다.
      감사합니다.

  3. 핑백: 엑셀함수 MATCH - 특정 범위에서 값의 위치 찾기 - XLWorks - XLWorks

  4. 핑백: VLOOKUP으로 안될때 INDEX,MATCH 사용하기 - XLWorks

답글 남기기

이메일 주소를 발행하지 않을 것입니다.