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($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 함수는 옳지않은 결과를 반환합니다. 따라서 찾을범위(세로)는 반드시 '고유값'이 들어있는 범위로 지정해야합니다.

2. 찾을범위(가로)에서 열번호를 반환합니다.

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

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

아래 예제에서는 가로범위 6번째에 위치한 '최소주문수량'을 선택하였으므로 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박스'를 결과값으로 출력합니다.

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)
  참조범위에서 주로 머릿글이 입력된 범위를 지정합니다. 그 후 원하는 값을 검색한 뒤, 참조범위에서 해당값이 가로로 몇번째에 위치하는지 열번호를 반환합니다. 범위의 경우 자동채우기 오류를 방지하기 위해 절대참조하는 것에 주의합니다.