엑셀 INDEX 함수 사용법과 MATCH 함수와 함께 동적 데이터 검색하는법 알아보기

엑셀 INDEX 함수는 실무에서 많이 활용하는 함수 중 하나이다.

특정 데이터 범위에서 원하는 값을 검색하고 추출할 때 사용된다.

큰 데이터베이스나 복잡한 데이터 구조를 다룰 때 효과적으로 데이터를 관리하고 분석할 수 있게 도움을 주는 함수로,

INDEX 함수 사용방법과 예제를 통한 쉬운 이해,

그리고 같이 많이 활용하는 MATCH 함수, VLOOKUP 함수를 통한 응용까지 알아보도록 하겠다.

 

엑셀 INDEX 함수

엑셀 INDEX 함수는 지정한 범위에서 몇 번째 위치하는 값을 출력할 것인가에 대한 함수이다.

구문과 예제를 통해 쉽게 이해해보자

=INDEX(배열, 행번호, [열번호])
=INDEX(array, row_num, [column_num]) 

 

• 배열 : 값을 검색할 데이터의 범위 또는 배열
• 행번호 : 출력할 값의 가로방향 순번 (행의 위치)
• 열번호 : 출력할 값의 세로방향 순번 (열의 위치)

 

대괄호로 표현한 것은 선택 인수이기 때문이다.

필수로 입력하는 것이 아니라 굳이 필요없다면 입력하지 않아도 무방하다는 뜻!

 

위 구문을 쉽게 해석하면,

범위 또는 배열 기준으로 몇 행, 몇 열의 데이터를 출력할꺼야? 라는 뜻이다.

선택한 데이터의 범위에서 2행 3열의 데이터를 출력해줘라고 한다면,

=INDEX(범위/배열, 2, 3) 이렇게 하면 된다.

 

예제를 통해 쉽게 이해해보자!

엑셀 index 함수 예제

 

간단하게 메뉴와 가격이 있는 데이터가 있다고 했을 때,

아이스아메리카노 또는 아이스라떼의 가격을 출력하고 싶다고 하면

가격의 범위는 D3:D14이기 때문에 배열은 D3:D14가 되고, 아이스아메리카노는 1행에 있으니

=INDEX(D3:D14,1)

요렇게 써주면 범위에서의 1행 값인 6,000원이 반환되는 것이다.

 

만약 범위가 다중이라면 행번호에 이어 열번호까지 필요해지게 되는 것이다.

엑셀 index 함수 예제 2번째

 

지역이라는 컬럼이 추가되었다고 하면 데이터의 범위는 C3:D14가 될것이고,

이 때 아이스라떼는 범위 내에서 2행 1열에 위치하기 때문에,

=INDEX(C3:D14, 2, 1)

요렇게 써주면 범위에서 2행 1열에 위치한 경기수원이 값으로 반환될 것이다.  

 

동적 데이터 검색을 위해 MATCH 함수 활용하기

하지만 이건 내가 몇 행 몇 열에 원하는 값이 있다는걸 알 때 얘기이고,

데이터 양이 많아서 내가 알지 못할 때는 일일히 저렇게 행과 열의 값을 넣어주기 쉽지 않다.

INDEX 함수 자체적으로만 실무에서 활용하는 일은 아마 많지 않을 것이다.

그래서 내가 원하는 값에 매칭되는 다른 값들을 불러오기 위해서 MATCH 함수를 함께 활용할 수 있다.

MATCH 함수는 내가 원하는 값이 몇 번째에 있는지를 반환해주는 함수이다.

=MATCH(찾으려는 값, 셀 범위, [일치 방법])
=MATCH(lookup_value, lookup_array, [match_type])

  

• 찾으려는 값 : 셀 범위(lookup_array)에서 찾으려는 값이다.
• 셀 범위 : 검색할 셈 범위이다.
• 일치 방법 : 1, 0, -1로 고를 수 있는데 default값은 1이다. 1은 작거나 같은 값 중 최대값을, -1은 크거나 같게하는 값 중 작은값을 반환한다. 0은 동일한 값의 첫 번째 값이다.

 

엑셀 index, match 함수 적용 예제

 

INDEX 함수의 행 위치를 MATCH 함수의 결과값으로 반환해주도록 만드는 것이다.

아이스아메리카노는 언제든 변할 수 있는 값이 되는 것이고 그 값에 따라 지역과 가격을 뱉어낼 수 있게 되는 것이다.

 

=INDEX(C3:D14

메뉴에 따라 지역과 가격의 결과값을 뱉어내야 하기 때문에 INDEX 함수에서의 범위, 배열은 C3:D14가 된다.

 

=INDEX(C3:D14, MATCH(F4, B3:B14, 0)

행의 위치는 MATCH 함수의 결과값으로 반환된다.

input 값이 되는 메뉴는 F4인 아이스아메리카노 이기 때문에 F4로 설정,

해당 메뉴의 범위인 B3:B14을 검색할 셀 범위로 지정하고,

정확하게 일치하는 값으로 하기 위해 일치 방법은 0으로 설정하였다.

 

=INDEX(C3:D14, MATCH(F4, B3:B14, 0), 1)

마지막으로 INDEX 함수의 열번호는 지역의 경우 1, 가격의 경우 2가 되겠다.

 

수식 복사를 해서 가격 쪽에도 붙여넣기를 해놓으면,

F4에 어떤 메뉴를 내가 입력하냐에 따라 지역과 가격이 자동적으로 바뀌게 된다.

 

MATCH 함수의 경우 다중조건을 입력할 수도 있다.

조건1 * 조건2 * 조건3 ... 으로 입력하면서 모든 조건을 만족시키는 값을 반환시키도록 할 수 있다.

배열의 곱셈을 통해 모두 만족시키는 값만 반환하도록 하는 것이다.

   

INDEX 함수와 MATCH 함수를 활용하여 동적 데이터 검색하는 방법은 VLOOKUP 함수와 동일한 결과를 볼 수 있다.

VLOOKUP 함수를 여러개의 조건으로 검색하여 사용할 수 있다는 것이 INDEX 함수와 MATCH 함수의 응용 공식이다.

VLOOKUP 함수 관련한 내용은 아래 포스팅 참고하시길!

엑셀 vlookup 함수 사용방법과 자주 발생하는 오류 해결방법