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

실무자라면 꼭 알아야 할 INDEX/MATCH 함수의 동작원리와 기초예제를 단계별로 알아봅니다.

홈페이지 » 엑셀 INDEX MATCH 함수, 세상에서 제일 쉽게 알아보기

작성자 :
오빠두엑셀
최종 수정일 : 2021. 05. 14. 01:02
URL 복사
평점 남기기 : (12)

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

엑셀 INDEX MATCH 함수 목차 바로가기
영상강의

큰 화면으로 보기

예제파일 다운로드

오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.

  • [실무기초] 엑셀 INDEX MATCH 함수 기초
    예제파일
  • [실무기초] 엑셀 INDEX MATCH 함수 기초
    완성파일

.

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

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


VLOOKUP 함수의 5가지 제한사항

VLOOKUP 함수는 실무에서 가장 중요한 함수입니다. 직장인 대상으로 엑셀에서 가장 자주 사용하는 함수를 설문조사하면, SUMIF 함수와 함께 항상 1,2위로 선정되는 매우 중요한 함수인데요. 하지만 VLOOKUP 함수를 실무에서 사용하면 종종 겪게되는 5가지 제한사항이 있습니다.

  1. VLOOKUP 함수는 왼쪽방향으로 조회가 불가능합니다.
    VLOOKUP 함수는 찾을범위 기준 오른쪽으로만 값을 조회할 수 있습니다. 즉 찾을범위는 항상 범위의 맨 왼쪽에 있어야 하는데요. 만약 찾을범위 기준 왼쪽으로 값을 조회해야 할 경우에는 이번강의에서 알아 볼  INDEX/MATCH 함수를 사용하거나 365 사용자일 경우 XLOOKUP 함수를 사용할 수 있습니다.

    VLOOKUP 함수 오른쪽 방향 조회
    VLOOKUP 함수는 찾을 범위의 오른쪽으로만 조회할 수 있습니다.
  2. 첫번째로 일치하는 값만 출력합니다.
    VLOOKUP 함수는 위에서 맨 첫번째로 일치하는 값만 반환할 수 있습니다. 따라서 맨  마지막으로 일치하는 값을 찾거나 n번째로 일치하는 값을 찾으려면 다른 공식을 사용해야 합니다.

    VLOOKUP 함수 첫번째 값 반환
    VLOOKUP 함수는 첫번째로 일치하는 값을 반환합니다.
  3. 하나의 조건으로만 검색 가능합니다.
    VLOOKUP 함수는 하나의 조건으로만 검색가능합니다. 예를 들어 제품명이 "오리털파카"이면서 사이즈가 "S" 인 값은 VLOOKUP 함수로 조회할 수 없습니다. 만약 여러개 조건을 동시에 만족하는 값을 검색해야 하려면 VLOOKUP 다중조건 공식을 사용해야 합니다.

    VLOOKUP 함수 다중 조건
    VLOOKUP 함수는 여러 조건을 일치하는 조건을 검색할 수 없습니다.
  4. 하나의 결과만 반환할 수 있습니다.
    VLOOKUP 함수의 조건 범위 안에 일치하는 값이 여러개 있을 경우, VOOKUP 함수는 하나의 결과만 반환합니다. 만약 일치하는 모든 조건을 반환하려면 VLOOKUP 여러개 값 반환하기 공식을 사용하거나 365 사용자일 경우 FILTER 함수를 사용하여 해결합니다.

    VLOOKUP 함수 여러개 값 반환
    VLOOKUP 함수는 여러 결과를 동시에 반환할 수 없습니다.
  5. 세로로 긴 하나의 열 대상으로만 값을 찾을 수 있습니다.
    VLOOKUP 함수는 세로로 긴 하나의 열을 조건범위로만 참조할 수 있습니다. 만약 여러개 열을 동시에 조회하려면 넓은 범위를 참조하는 VLOOKUP 공식을 사용해야 합니다.

    VLOOKUP 함수 넓은 범위 조회
    VLOOKUP 함수는 하나의 열에서만 찾을 값을 검색할 수 있습니다.

MATCH 함수 & INDEX 함수 기초

MATCH 함수 동작원리

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

= MATCH ( 찾을값, 범위, [일치옵션] )

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

= MATCH ( "카푸치노", 메뉴범위 )
'카푸치노가 메뉴범위에서 몇번째 위치하는지 순번을 반환합니다.
MATCH 함수 동작원리
카푸치노가 메뉴범위에서 몇번째 위치하는지 순번을 반환합니다.
오빠두Tip : MATCH 함수의 일치옵션 기본값은 유사일치입니다. 그리고 일치옵션을 유사일치로 사용하려면 대상 범위가 반드시 오름차순 또는 내림차순으로 정렬되어야 합니다. 따라서 실무에서는 대부분의 경우 MATCH 함수의 일치옵션으로 0(=정확히 일치)을 사용합니다.
INDEX 함수 동작원리

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

= INDEX ( 범위, 행번호, [열번호], [배열번호] )

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

= INDEX ( 가격범위, 4 )
'가격 범위에서 4번째 위치한 값을 반환합니다.
INDEX 함수 동작원리
가격 범위에서 4번째 위치하는 값을 반환합니다.
오빠두Tip : INDEX 함수 함수에 대한 자세한 설명은 아래 INDEX 함수 관련 포스트를 참고하세요.

INDEX/MATCH 함수 언제 사용하나요?

INDEX MATCH 함수는 위에서 언급한 VLOOKUP 함수의 5가지 제한사항으로 인해 발생하는 여러가지 문제를 해결할 때 사용합니다. 특히 실무에서는 여러개의 조건을 만족하는 VLOOKUP 함수(다중조건 VLOOKUP) 또는 왼쪽 방향으로 조회하는 VLOOKUP 함수를 구현할 때 자주 사용됩니다.

이번 강의에서는 INDEX MATCH 함수의 기초내용인 VLOOKUP 함수를 왼쪽으로 조회하는 공식에 대해 알아보겠습니다.

INDEX/MATCH 기초 예제

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

INDEX MATCH 함수 사용 상황
메뉴판의 맨 오른쪽에 메뉴가 입력되어 있어 VLOOKUP 함수를 사용할 수 없습니다.

메뉴판을 보면 메뉴가 표의 가장 오른쪽에 위치하고 있어, VLOOKUP 함수로는 메뉴를 참조하여 가격을 조회할 수 없는데요. 이럴 경우 INDEX/MATCH 함수를 사용하여 문제를 해결할 수 있습니다.

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

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

    MATCH 함수 입력
    MATCH 함수를 입력합니다.
  3. 방금전에 입력한 MATCH 함수를 아래와 같이 INDEX 함수로 묶어줍니다. INDEX 함수로 묶어주면, TALL 가격 범위에서 카푸치노의 순번과 동일한 위치의 값을 결과로 반환합니다.

    엑셀 INDEX MATCH 함수 완성
    INDEX 함수로 MATCH 함수를 감싸서 입력하면 INDEX MATCH 함수가 완성됩니다.
  4. 메뉴를 변경하면 각 메뉴의 TALL 사이즈 가격이 반환됩니다.
    index match 함수 완성

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

INDEX/MATCH 함수는 실무에서 다양한 상황에 응용할 수 있습니다. INDEX/MATCH 함수를 활용한 각종 공식은 아래 목록을 참고해주세요.

  • 다중조건 VLOOKUP 공식
    : 여러 조건을 만족하는 VLOOKUP 검색 공식입니다.

    = { INDEX(출력범위, MATCH(1, (조건1=조건범위1)*(조건2=조건범위2)*..., 0)) }
  • VLOOKUP 함수 여러개 결과 출력 공식
    : 범위 안에 조건을 만족하는 값이 여러개 있을 경우 여러 결과를 동시에 반환하는 공식입니다.

    = { INDEX($출력범위, SMALL(IF(($찾을값=$찾을범위),MATCH(ROW($찾을범위), ROW($찾을범위)), ""), ROWS($A$1:A1))) }
  • 그림을 출력하는 VLOOKUP 공식
    : VLOOKUP 결과로 이미지를 출력하는 공식입니다.

    = INDEX(그림범위, MATCH(항목, 항목범위, 0 ))
  • 마지막 값을 출력하는 VLOOKUP 공식
    : 범위에서 마지막으로 일치하는 값을 반환합니다.

    = LOOKUP (2,1/(찾을범위=찾는값),출력범위)
  • n번째 값을 출력하는 VLOOKUP 공식
    : 범위에서 두번째, 세번째..(n번째)로 일치하는 값을 반환합니다.

    = { INDEX($출력범위,SMALL(IF($찾을값=$찾을범위,ROW($찾을범위)-ROW($시작셀)+1),N번째)) }
5 12 투표
게시글평점
guest
20 댓글
Inline Feedbacks
모든 댓글 보기
20
0
여러분의 생각을 댓글로 남겨주세요.x