[엑셀] INDEX 함수 & MATCH 함수 사용하기

안녕하세요 ,김데이 입니다. 

어제는 제가 많이 애정하는 "VLOOKUP 함수"에 대해서 설명 해 드렸습니다. 애정하는 함수이지만 VLOOKUP 함수는 기준값이 꼭 왼쪽에 있어야 하고, 불러와야 하는 데이터가 오른쪽에 있어야만 값을 가져올수 있다는 단점이 있다고 설명 드렸는데요. 그래서 오늘은 VLOOKUP 함수의 단점을 보안할 수 있는 INDEX 함수 & MATCH 함수 조합을 알려드리고자 합니다!

 

INDEX 함수 & MATCH 함수의 사용법 및 조합

 

---

 

INDEX ( 찾을 범위, 행 번호, 열 번호 )

INDEX 함수는 전체 범위 내에서 행 & 열 이 겹치는 셀의 값을 구하는 함수 입니다. 어려운 함수가 아니니 바로 예시와 사용법에 대해 설명 해 보겠습니다. 

 

INDEX 함수의 예시

데이터의 전체 범위 중에서 행 번호 = 세로 순서 / 열 번호 = 가로 순서 를 각각 지정 해 주면 해당하는 위치의 값을 가져오게 됩니다. 

 

1) 데이터를 찾을 범위를 선택 합니다. 

=INDEX(찾을 범위, 행 번호, 열 번호)

데이터를 찾을 범위를 선택

 

 

2) 범위 중 찾을 위치의 행 번호를 입력 합니다. 데이터의 첫번째 세로 줄이 1번으로 시작 됩니다.

=INDEX(찾을 범위, 행 번호, 열 번호)

찾을 위치의 행 번호 입력

 

 

3) 범위 중 찾을 위치의 열 번호를 입력 합니다. 데이터의 첫번째 가로 줄이 1번으로 시작 됩니다.

=INDEX(찾을 범위,행 번호, 열 번호)

찾을 위치의 열 번호 입력

 

INDEX 함수 사용 방법은 비교적 간단합니다. 여기에 한가지 팁을 더 드리자면, 데이터가 1열만 있는 경우에는 마지막에 쓰는 "열 번호"를 생략하셔도 사용 가능합니다. 

INDEX 함수에서 "열 번호" 생략 한 결과

크게 중요한 내용은 아니지만 언급 한 이유는 INDEX & MATCH 조합에서 "열 번호"는 생략해서 사용할 예정이기 때문이죠^^ 작성하는 값이 하나라도 줄어들면 쉽게 작성할 수 있으니까요 👍

 

 

 

 

MATCH ( 찾을 값, 찾을 범위, 참조 기준 )

MATCH 함수는 내가 찾고 싶은 값이 특정 범위(=찾을 범위)에서 몇 번째에 있는지 알려줍니다. 여기에서 "참조 기준"은 "찾을 값"과 "범위 내의 값" 사이의 정확도에 대해서 결정 해 주는 것 인데요, 자세한 내용은 사용법을 통해서 알아보도록 하겠습니다. 

 

MATCH 함수의 예시

 

 

1) 찾을 값을 선택 합니다. 

=MATCH(찾을 값, 찾을 범위, 참조 기준)

찾을 값을 선택

 

2) 어디에서 찾을 지 데이터 범위를 선택 합니다. 단, 열/행 구분 없이 딱 한 줄만 선택이 가능합니다. 

=MATCH(찾을 값, 찾을 범위, 참조 기준)

찾기 위한 데이터 범위 지정

 

 

3) 참조 할 데이터의 정확도를 지정 합니다. 

=MATCH(찾을 값, 찾을 범위, 참조 기준)

-1 : 찾을 값 보다 이하 값(같거나 작은 값) 중에서 가장 큰 값을 불러옴

0 : 정확히 일치하는 값 불러옴

1 : 찾을 값 보다 이상 값(같거나 큰 값) 중에서 가장 작은 값을 불러옴

참조 기준을 설정

 

다른 함수들과 마찬가지로 정확도를 설정하는 "참조 기준"은 대부분의 상황에서 "0"을 선택하게 됩니다. 나머지 "-1"과 "1"의 사용법에 대해서는 간단히 참고만 해 주시면 됩니다. 

 

위에서 잠시 언급 한 부분인데 데이터의 범위는 행과 열 상관 없이 1줄만 선택하면 결과가 나옵니다. 하지만 이를 지키지 않고 여러 줄을 선택 한 경우 에러 메세지가 나오게 되니 꼭 1개의 행 또는 열을 선택 하시기 바랍니다. 

데이터 범위를 행과 열을 복합적으로 선택 한 경우(3개 열 + 3개 행) : 에러 발생

 

 

 

 

INDEX 함수 & MATCH 함수 결합하기 

두 개의 함수가 어느정도 이해가 되셨다면 이번에는 VLOOKUP 함수의 단점을 보완한 결합 함수를 설명 해 드리도록 하겠습니다.

INDEX 함수 + MATCH 함수 결합의 기본 구조

 

먼저 MATCH 함수를 통하여 우리가 원하는 값의 셀이 몇번째 행에 있는지 구한 뒤, INDEX 함수를 통하여 결과 값을 도출하게 만드는 방법입니다. 말은 거창하지만 아래의 사용법을 보시면 의외로 간단합니다 😁

 

 

 Q. "사번"을 기준으로 "이름"을 구하시오 

1) MATCH 함수를 통하여 내가 찾을 값의 "행 번호(기준 값)"가 몇번인지 구합니다. 

  "사번"을 기준으로 "이름"을 찾고자 하기 때문에 일단 "사번"이 몇번째에 있는지 구하는 것이 우선입니다.

  그래서 MATCH 함수에 [ 찾을값 = 사번 값 / 찾을 범위 = 사번의 전체 범위 / 참조 기준 = 0(정확히) ] 설정 합니다

=INDEX(찾을 범위,MATCH(찾을 값, 찾을 범위,참조 기준))

INDEX 함수에 사용 할 "행번호(기준값)"를 구하기 위해 MATCH 함수를 설정을 진행

 

 

2) MATCH 함수를 통해 "행 번호"를 구했다면 이번엔 INDEX 함수에서 범위를 지정 해 주어 원하는 값을 찾아 줍니다. 

우리가 MATCH 함수로 찾은 사번 "20220915KHD"는 2번째에 있으므로, 이것이 INDEX 함수에 적용 되면 행 번호 숫자에 "2"가 적용 됩니다.

=INDEX(찾을 범위,MATCH(찾을 값, 찾을 범위,참조 기준))

INDEX 함수에 적용하기 위하여 "찾을 범위"를 지정

 

이렇게 되면 최종적으로 어떠한 "사번"을 입력하더라도 "이름"을 출력할 수 있는 함수가 만들어 집니다. 

최종 결과 "사번"을 입력하면 "이름"이 나오게 됨

 

---

이렇게 INDEX 함수와 MATCH 함수의 조합으로 VLOOKUP 함수에서는 할 수 없었던 [ 기준 값이 "오른쪽" / 찾을 데이터가 "왼쪽" ] 인 경우에 대해서도 충분히 원하는 값을 만들어 낼 수 있게 됩니다.

 

사실 초보 분들이 아니더라도 두 개 함수를 한번에 사용하면 헷갈리고 복잡하게 느껴질 수 있습니다. 그래서 최근 엑셀 프로그램 자체에 "XLOOKUP" 이라는 함수가 생겨 났습니다. 하지만 신규 버전에서만 "XLOOKUP" 함수의 사용이 가능하기 때문에 여전히 보편적으로 사용하고 있는 INDEX & MATCH 함수 조합에 대해서 다뤄 보았습니다. 

 

바로는 아니더라도 조만간 "XLOOKUP" 함수에 대해서도 꼭 다뤄 보도록 하겠습니다😉

 

함께 보면 좋은 글 

[엑셀] VLOOKUP 함수 기본 설명 & 응용