엑셀 INDEX MATCH 함수 고급 사용법 :: 엑셀 함수 공식

INDEX / MATCH 함수의 고급 응용 공식의 동작원리와 실전 예제를 알아봅니다.

홈페이지 » 엑셀 INDEX MATCH 함수 고급 사용법 :: 엑셀 함수 공식

INDEX MATCH 함수 고급 사용법:: 엑셀 함수 공식

함수기본공식

1. 열 위치를 자동으로 반환하는 INDEX/MATCH 공식

= INDEX($참조범위, MATCH(검색값(세로), $찾을범위(세로), 0), MATCH(검색값(가로), $찾을범위(가로), 0))
'// 참조범위 : 행/열 번호를 기준으로 값을 검색할 전체 범위입니다. (VLOOKUP의 참조범위와 동일)
'// 검색값(세로) : 찾을범위(세로) 에서 찾을 값입니다. [주로 고유값 (제품명, 아이디 등..) ]

'// 찾을범위(세로) : 참조범위 중, 검색값(세로)를 찾을 고유값 범위입니다.
'// 검색값(가로) : 찾을범위(가로) 에서 찾을 값입니다. [주로 머릿글]
'// 찾을범위(가로) : 참조범위 중, 검색값(가로)를 찾을 머릿글 범위입니다.

2. 여러개의 조건을 비교하는 INDEX/MATCH 공식

= { INDEX(출력범위, MATCH(1,(조건1=조건범위1)*(조건2=조건범위2)*…), 0)) }
'// 출력범위 : 여러개의 조건을 만족할 시, 값을 출력할 범위입니다.
'// 조건1, 조건2... : 조건범위에서 비교할 조건입니다.
'// 조건범위1, 조건범위2, ... : 조건들을 비교할 조건범위입니다.
VLOOKUP 다중조건 썸네일 최종크기
사용된 공식 = { INDEX(출력범위, MATCH(1, (조건1=조건범위1)*(조건2=조건범위2)*..., 0)) } [! 주의] 해당 공식은 배열수식이므로 Ctrl + Shift + Enter 로 '꼭! ... 더 보기

3. 이미지를 찾아서 출력하는 INDEX/MATCH 공식

= INDEX(사진범위, MATCH(찾을값, 참조범위, 0)) [사용자 지정범위 설정]
'// 사진범위 : 그림을 불러올 이미지가 들어있는 사용자 지정범위입니다.
'// 찾을값 : 참조범위에서 검색할 값입니다.
'// 참조범위 : 찾을값을 검색한 뒤, 동일한 행에 위치한 사진을 반환하기 위한 참조범위입니다.
'// 해당 공식을 또 다른 사용자지정범위로 설정 후, 연결된 이미지에 사용자지정범위를 입력합니다.
VLOOKUP 함수 이미지 불러오기 공식_썸네일
함수 공식 = INDEX(그림범위, MATCH(항목, 항목범위, 0 )) 영상 강의 보러가기 VLOOKUP 이미지 불러오기 공식을 보다 쉽게 설명해드리기 위해 영상강의를 준비해 ... 더 보기

예제파일 다운로드

예제파일
INDEX/MATCH 함수는 알아두면 여러상황에서 다양하게 응용할 수 있는 다재다능한 공식입니다.

INDEX/MATCH 함수에 대한 기초설명이 필요하신 분은 관련포스트가 준비되어있으니 아래 링크를 참고하세요.

INDEX MATCH 함수 예제
목차 INDEX/MATCH 함수 세상에서 제일 쉽게 알아보기 - 영상강의 INDEX MATCH 함수 vs. VLOOKUP 함수 비교 INDEX/MATCH 함수 공식 설명 ... 더 보기

INDEX 함수MATCH 함수에 대한 기초함수설명이 필요하신분은 실전 예제와 함께 함수에 대한 자세한 설명이 준비되어 있습니다. 아래 링크를 참고하세요.

엑셀 MATCH 함수 사용법 썸네일_크기
엑셀 MATCH 함수 목차 바로가기 함수 요약 MATCH 함수 구문 인수 알아보기 MATCH 함수 상세설명 MATCH 함수 사용법 간단예제 예제파일 ... 더 보기
엑셀 INDEX 함수 사용법
요약 엑셀 INDEX 함수는 범위에서 특정 순번에 위치하는 값을 반환하는 함수입니다. (또는 셀 주소를 반환할 수도 있습니다.) 즉, 선택한 범위에서 ... 더 보기

사용된 함수

본 포스트에서는 열 위치를 자동으로 반환하는 INDEX/MATCH 함수의 설명을 다룹니다.

=INDEX($B$7:$G$16,MATCH(B19,$B$7:$B$16,0),MATCH(C19,$B$7:$G$7,0))

함수의 동작 원리

1. 찾을범위(세로)에서 행번호를 반환합니다.

=INDEX($B$7:$G$16,MATCH(B19,$B$7:$B$16,0),MATCH(C19,$B$7:$G$7,0))

MATCH 함수를 사용합니다. B7:B16[제품명이 입력된 범위]에서 찾고자 하는 값인 '3종 케이스세트'가 위치한 순번을 반환합니다. 해당 값은 6번째 위치하므로 MATCH 함수는 6을 반환합니다.

세로로 찾는 범위에서 동일한 값이 2개 이상 있을 경우, INDEX/MATCH 함수는 옳지않은 결과를 반환합니다. 따라서 찾을범위(세로)는 반드시 '고유값'이 들어있는 범위로 지정해야합니다.

INDEX MATCH 함수 고급 세로범위
세로범위에서 '3종 케이스세트'의 순번인 6을 반환합니다.
2. 찾을범위(가로)에서 열번호를 반환합니다.

=INDEX($B$7:$G$16,MATCH(B19,$B$7:$B$16,0),MATCH(C19,$B$7:$G$7,0))

행번호를 찾을때와 동일합니다. 마찬가지로 가로로 지정된 범위에 동일한 값이 2개이상 있을경우, 옳지않은 결과를 반환하므로 주의합니다.

아래 예제에서는 가로범위 6번째에 위치한 '최소주문수량'을 선택하였으므로 MATCH함수는 6을 반환합니다.

INDEX MATCH 함수 고급 가로범위
가로범위에서 '최소주문수량'이 위치한 순번인 6을 반환합니다.
3. 참조범위에서 각 행/열 순번에 위치한 값을 반환합니다.

=INDEX($B$7:$G$16, MATCH(B19,$B$7:$B$16,0), MATCH(C19,$B$7:$G$7,0))
=INDEX($B$7:$G$16, 6, 6) '// 각 MATCH 함수에서 행/열번호를 받아옵니다.
=1박스

각 MATCH 함수에서 참조범위의 행/열번호를 받아왔습니다. 아래 예제에서는 세로범위에서 '3종케이스세트' (6번째 위치), 가로범위에서 '최소주문수량'(6번째 위치)를 검색하였습니다.

따라서 참조범위에서 세로로 6번째, 가로로 6번째에 위치한 '1박스'를 결과값으로 출력합니다.

INDEX MATCH 함수 사용예제
참조범위에서 세로로 6번째, 가로로 6번째에 위치한 값인 '1박스'를 출력합니다.
4. 구간별로 알아보기

=INDEX($B$7:$G$16,MATCH(B19,$B$7:$B$16,0),MATCH(C19,$B$7:$G$7,0))

  • 참조범위 : $B$7:$G$16
    값을 참조할 전체 범위를 선택합니다. 자동채우기시 오류를 방지하기 위해 키보드 F4키를 눌러 절대참조 하는것에 주의합니다.
  • 행번호반환(세로) : MATCH(B19, $B$7:$B$16,0)
    참조범위에서 주로 고유값(주로 제품번호 또는 아이디 등)이 세로로 입력된 범위를 지정합니다. 해당 범위에 값을 검색한 뒤 세로로 몇번째에 위치하는지 행번호를 반환합니다. 범위의 경우 자동채우기 오류를 방지하기 위해 절대참조합니다.
  • 열번호반환(가로) : MATCH(C19, $B$7:$G$7, 0)
    참조범위에서 주로 머릿글이 입력된 범위를 지정합니다. 그 후 원하는 값을 검색한 뒤, 참조범위에서 해당값이 가로로 몇번째에 위치하는지 열번호를 반환합니다. 범위의 경우 자동채우기 오류를 방지하기 위해 절대참조하는 것에 주의합니다.
INDEX MATCH 함수 고급사용법 열 번호 자동 반환
MATCH 함수를 가로와 세로범위에 모두 적용합니다.

기초함수 설명 :: by 오빠두엑셀

엑셀 VLOOKUP 함수 사용법 썸네일크기
엑셀 VLOOKUP 함수 사용법 총정리 :: 참조 함수
VLOOKUP 함수 목차 바로가기 함수 구문 및 인수 예제파일 다운로드 함수 설명 VLOOKUP함수 사용시 주의사항 자주묻는질문1. #N/A, #REF, #VALUE, #NAME ...
엑셀 MATCH 함수 사용법 썸네일_크기
엑셀 MATCH 함수 사용법 및 실전예제 :: 참조 함수
엑셀 MATCH 함수 목차 바로가기 함수 요약 MATCH 함수 구문 인수 알아보기 MATCH 함수 상세설명 MATCH 함수 사용법 간단예제 예제파일 ...

관련된 함수공식 :: by 오빠두엑셀

VLOOKUP 참조 범위의 마지막 값을 반환합니다.
지정한 범위에서 찾는값과 일치하는 그림을 출력합니다.
범위에서 값을 찾아 같은 행의 다른 값을 반환합니다.
여러 조건을 만족하는 VLOOKUP 검색 공식입니다.
2중 조건을 만족하는 데이터유효성 목록상자를 생성합니다.
Loading...
5 16 투표
게시글평점
guest
28 댓글
Inline Feedbacks
모든 댓글 보기
finepoint
finepoint
2019년 9월 11일 4:16 오후

안녕하세요. 평소에 친절하게 설명해주시는 엑셀강의 정말 잘 보고 있습니다. 덕분에 많은 도움을 받고 있습니다. 궁금한 점이 있어 댓글 남깁니다. 실무에서 index-match함수를 가끔 사용하는데 2가지 조건하에서는 잘 활용하는데 3가지 이상의 조건에서는… 더보기 »

오빠두엑셀
2019년 9월 13일 10:43 오전
답글 남기기  finepoint

안녕하세요? 오빠두엑셀입니다.
MATCH 함수를 아래와 같이 사용해보세요.
MATCH(1,( --(조건범위=조건)*--(조건범위=조건)*...),0)
감사합니다!

박상준
박상준
2020년 3월 11일 2:19 오후
답글 남기기  오빠두엑셀

안돼요 ㅠㅠ 예제하나 올려주시면 안될까요?..

몽이아빠
몽이아빠
2020년 7월 11일 10:08 오후
답글 남기기  오빠두엑셀

이렇게 배열 적용시..행과 열의 배열위치는 정해져 있지 않는지요?
저도 2개 조건은 쉽게 되는데..2개 이상의 조건(특히 행열 조건을 같이 줘야 할때..잘안되서..
조건 앞에 --를 붙여 보라는 말씀 이신지?

하늬돌이
하늬돌이
2020년 2월 9일 11:08 오전
게시글평점 :
     

좋은 설명 감사해요.

초코
초코
2020년 3월 4일 10:13 오후
게시글평점 :
     

좋은정보 감사합니다

daniel_p
daniel_p
2020년 3월 22일 9:06 오후
게시글평점 :
     

많은 도움이 되었습니다... 감사합니다~

sun3148
sun3148
2020년 3월 25일 10:32 오전
게시글평점 :
     

제가 정말 원했던 기능에 대해서 설명을 너무 자세하고 쉽게 잘 해주셨네요. 빨리 적용해 봐야겠어요. ^^

쌍둥아빠
쌍둥아빠
2020년 3월 29일 6:04 오후
게시글평점 :
     

잘 보고 갑니다.

갓쩌리
갓쩌리
2020년 4월 3일 2:24 오후
게시글평점 :
     

이정도면 필터 기능이 없는 오피스2010에 적용할 만한 조합인것 같네요.

토토가
토토가
2020년 4월 4일 5:15 오후
게시글평점 :
     

배열함수 팁으로 여러조건이 가능하네요 감샤합니다~~

엑셀기초
엑셀기초
2020년 4월 15일 9:30 오후
게시글평점 :
     

좋은 설명 감사해요

zi****
zi****
2020년 6월 5일 3:48 오후
게시글평점 :
     

좋은자료 감사드립니다.

유태욱
유태욱
2020년 9월 6일 5:50 오후
게시글평점 :
     

감사합니다. INDEX - MATCH - SMALL 함수를 통해서 아래와 같이 했습니다. =IF(J8<K8,INDEX($A$17:$A$21,MATCH(SMALL($N$17:$N$21,R7),$N$17:$N$21,0)),INDEX($A$17:$A$21,MATCH(SMALL($M$17:$M$21,R7),$M$17:$M$21,0))) 문제는 동일한 값이 있을 경우 SMALL 함수 문제인지, 결과값이 동일하게 출력됩니다. 예를 들어, 가 - 160 / 나… 더보기 »

유태욱
유태욱
2020년 9월 23일 3:33 오후
답글 남기기  오빠두엑셀

감사합니다!

임세
임세
2020년 11월 12일 2:36 오후
게시글평점 :
     

좋은 설명 감사합니다

selene
selene
2021년 2월 9일 10:23 오후
게시글평점 :
     

조건 2~3개를 만족하며, 결과물(여러개가 나올경우) x번째 결과물이 출력되게는 못하나요?

엑셀엑셀
엑셀엑셀
2021년 3월 5일 12:50 오후
게시글평점 :
     

깔끔한 설명 감사합니다!

tormtroo
tormtroo
2021년 7월 20일 2:15 오후
게시글평점 :
     

안녕하세요 index-match는 잘 응용하고 있습니다!! 근데 궁금한점이 있어서 댓글 남깁니다!! 예제 에서 제품명/검색항목을 드롭다운처럼 항시 눌러서 선택할수 있게 되어있던데 이건 어떻게 하는 걸까요?? 궁금합니다!

Diogenes
Diogenes
2021년 7월 25일 11:01 오전
게시글평점 :
     

강의 정말 감사합니다.
엑린이는 수식 만드는 것에는 성공했지만, Ctrl+Shift+Enter를 몰라서 1시간을 헤맸네요. ㅠㅠ
대체 어떤 건 함수를 계속 연장해도 상관이 없는데, 어떤 경우에 {} 이게 들어가야하는지는 모르겠어용.

엑셀개초보
엑셀개초보
2021년 9월 3일 3:08 오후
게시글평점 :
     

감사합니다 형님 인생의 은인이십니다 ㅜㅜ

코끼리
코끼리
2021년 9월 14일 8:24 오전
게시글평점 :
     

어려웠지만, 예제파일 받아서 공부하렵니다.

28
0
여러분의 생각을 댓글로 남겨주세요.x