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

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

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

작성자 :
오빠두엑셀
최종 수정일 : 2021. 11. 10. 21:52
URL 복사
메모 남기기 : (36)

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, ... : 조건들을 비교할 조건범위입니다.

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

= INDEX(사진범위, MATCH(찾을값, 참조범위, 0)) [사용자 지정범위 설정]
'// 사진범위 : 그림을 불러올 이미지가 들어있는 사용자 지정범위입니다.
'// 찾을값 : 참조범위에서 검색할 값입니다.
'// 참조범위 : 찾을값을 검색한 뒤, 동일한 행에 위치한 사진을 반환하기 위한 참조범위입니다.
'// 해당 공식을 또 다른 사용자지정범위로 설정 후, 연결된 이미지에 사용자지정범위를 입력합니다.

예제파일 다운로드

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

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

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

사용된 함수

본 포스트에서는 열 위치를 자동으로 반환하는 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 함수를 가로와 세로범위에 모두 적용합니다.
5 22 투표
게시글평점
36 댓글
Inline Feedbacks
모든 댓글 보기
36
0
여러분의 생각을 댓글로 남겨주세요.x