엑셀 INDEX MATCH 함수, 세상에서 제일 쉽게 알아보기

실습 가이드

라이브 강의 전체영상도 함께 확인해보세요!

위캔두 회원이 되시면 매주 오빠두엑셀에서 진행하는 라이브강의 풀영상을 확인하실 수 있습니다.

VLOOKUP 함수의 5가지 제한사항

VLOOKUP 함수는 실무에서 가장 자주 사용되는 핵심 함수입니다. 직장인을 대상으로 한 설문조사에서도 SUMIF 함수와 함께 항상 사용 빈도 1·2위로 꼽히는 매우 중요한 함수인데요. 다만 VLOOKUP 함수를 실무에 적용하다 보면 종종 마주치게 되는 5가지 제한사항이 있습니다.

1. VLOOKUP 함수는 왼쪽 방향으로 조회할 수 없습니다.
   VLOOKUP 함수는 찾을범위를 기준으로 오른쪽 방향으로만 값을 조회할 수 있습니다. 즉, 찾을범위는 항상 표의 맨 왼쪽에 위치해야 하는데요. 만약 찾을범위 기준 왼쪽 방향으로 값을 조회해야 한다면 이번 강의에서 살펴볼 INDEX/MATCH 함수를 사용하거나, 365 사용자라면 XLOOKUP 함수를 활용하면 됩니다.

   

2. 첫 번째로 일치하는 값만 반환합니다.
   VLOOKUP 함수는 찾을범위에서 위에서부터 처음 일치하는 값 하나만 반환합니다. 따라서 마지막으로 일치하는 값이나 n번째로 일치하는 값을 찾아야 한다면 별도의 공식을 사용해야 합니다.

   

3. 하나의 조건으로만 검색할 수 있습니다.
   VLOOKUP 함수는 단일 조건으로만 값을 조회할 수 있습니다. 예를 들어 제품명이 "오리털파카"이면서 사이즈가 "S"인 값처럼 두 가지 이상의 조건을 동시에 만족하는 값은 VLOOKUP 함수만으로 찾을 수 없습니다. 여러 조건을 동시에 만족하는 값을 검색해야 한다면 VLOOKUP 다중조건 공식을 사용해야 합니다.

   

4. 하나의 결과만 반환할 수 있습니다.
   찾을범위 안에 일치하는 값이 여러 개 존재하더라도 VLOOKUP 함수는 하나의 결과만 반환합니다. 일치하는 모든 값을 함께 반환하려면 VLOOKUP 여러 개 값 반환 공식을 사용하거나, 365 사용자라면 FILTER 함수로 해결할 수 있습니다.

   

5. 세로로 긴 하나의 열만 조건범위로 사용할 수 있습니다.
   VLOOKUP 함수는 세로 방향의 단일 열만 조건범위로 참조할 수 있습니다. 여러 열을 동시에 조건범위로 활용해야 한다면 더 넓은 범위를 참조하도록 설계된 응용 VLOOKUP 공식을 사용해야 합니다.

   

MATCH 함수 동작원리

MATCH 함수는 찾는 값이 지정한 범위에서 몇 번째 위치에 있는지 그 순번을 반환하는 함수입니다.

예를 들어 MATCH 함수를 아래와 같이 사용하면, 메뉴범위에서 "카푸치노"의 순번인 6을 반환합니다.

INDEX 함수 동작원리

INDEX 함수는 지정한 범위 안에서 n번째 위치에 있는 값을 반환하는 함수입니다.

예를 들어 INDEX 함수를 아래와 같이 사용하면, GRANDE 사이즈 가격범위에서 4번째 위치한 값인 5,600을 반환합니다.

INDEX/MATCH 함수는 앞서 살펴본 VLOOKUP 함수의 5가지 제한사항으로 인해 발생하는 다양한 문제를 해결할 때 사용합니다. 특히 실무에서는 여러 조건을 동시에 만족하는 다중조건 검색이나, 왼쪽 방향으로 값을 조회해야 하는 상황에서 자주 활용됩니다.

이번 강의에서는 INDEX/MATCH 함수의 가장 기본 활용법인, VLOOKUP 함수로는 처리하기 어려운 왼쪽 방향 조회 공식을 단계별로 알아봅니다.

예제파일을 실행하면 K27:N36 범위에 스타벅스 메뉴판이 정리되어 있습니다.

메뉴판을 보면 메뉴 항목이 표의 가장 오른쪽에 위치해 있어, VLOOKUP 함수만으로는 메뉴를 기준으로 가격을 조회할 수 없는데요. 이런 상황에서는 INDEX/MATCH 함수를 활용해 문제를 해결할 수 있습니다.

1. L38셀에 검색할 메뉴를 입력합니다. 예제에서는 "카푸치노"를 입력해보겠습니다.

   

2. L38셀에 아래와 같이 MATCH 함수를 입력합니다. MATCH 함수는 메뉴범위에서 "카푸치노"가 몇 번째 위치하는지 순번을 반환합니다.

   

3. 앞서 입력한 MATCH 함수를 INDEX 함수로 한 번 더 감싸줍니다. INDEX 함수로 묶으면 TALL 가격 범위에서 카푸치노의 순번과 같은 위치에 있는 값이 결과로 반환됩니다.

   

4. L38셀의 메뉴를 변경하면 각 메뉴에 해당하는 TALL 사이즈 가격이 자동으로 반환됩니다.
   

INDEX MATCH 함수를 응용한 고급공식

INDEX/MATCH 함수는 실무의 다양한 상황에 폭넓게 응용할 수 있습니다. INDEX/MATCH 함수를 활용한 대표적인 응용 공식은 아래 목록을 참고하세요.

• 다중조건 VLOOKUP 공식
  : 여러 조건을 동시에 만족하는 값을 검색할 때 사용하는 공식입니다.
  = { INDEX(출력범위, MATCH(1, (조건1=조건범위1)*(조건2=조건범위2)*..., 0)) }
• VLOOKUP 함수 여러개 결과 출력 공식
  : 범위 안에 조건을 만족하는 값이 여러 개일 때, 일치하는 모든 값을 동시에 반환하는 공식입니다.
  = { INDEX($출력범위, SMALL(IF(($찾을값=$찾을범위),MATCH(ROW($찾을범위), ROW($찾을범위)), ""), ROWS($A$1:A1))) }
• 그림을 출력하는 VLOOKUP 공식
  : 조건에 해당하는 셀의 이미지를 결과로 반환하는 공식입니다.
  = INDEX(그림범위, MATCH(항목, 항목범위, 0 ))
• 마지막 값을 출력하는 VLOOKUP 공식
  : 범위 안에서 마지막으로 일치하는 값을 반환하는 공식입니다.
  = LOOKUP (2,1/(찾을범위=찾는값),출력범위)
• n번째 값을 출력하는 VLOOKUP 공식
  : 범위에서 두 번째, 세 번째 등 원하는 n번째로 일치하는 값을 반환하는 공식입니다.
  = { INDEX($출력범위,SMALL(IF($찾을값=$찾을범위,ROW($찾을범위)-ROW($시작셀)+1),N번째)) }