INDEX/MATCH 가로세로 양방향 검색
지원 버전 자세히 보기
엑셀 2013 이전엑셀 2016엑셀 2019엑셀 2021엑셀 2024M365웹 엑셀
세로 항목과 가로 머리글이 만나는 값을 검색하는 공식입니다.
인수 설명
01INDEX/MATCH 양방향 검색
=INDEX ( 전체범위, MATCH ( 세로값, 세로범위, 0 ), MATCH ( 가로값, 가로범위, 0 ) )인수 설명 자세히 보기(인수를 클릭하면 해당 영역만 강조됩니다. 여러 개를 함께 켜고 끌 수 있습니다.)
인수구분설명
전체범위필수세로 위치와 가로 위치가 만나는 값을 반환할 데이터 범위입니다.
세로값필수세로범위에서 검색할 값입니다.
세로범위필수세로값의 행 위치를 찾을 단일 열 범위입니다. 같은 값이 여러 개면 MATCH 함수가 첫 번째 위치를 반환합니다.
가로값필수가로범위에서 검색할 머리글입니다.
가로범위필수가로값의 열 위치를 찾을 머리글 범위입니다. 같은 머리글이 여러 개면 MATCH 함수가 첫 번째 위치를 반환합니다.
A
B
C
D
E
F
G
H
I
1
제품명
단가
재고
최소주문수량
찾을 제품
찾을 항목
검색 결과
2
노트세트
12000
18
2박스
3종 케이스세트
최소주문수량
1박스
3
메모패드
5000
32
5권
4
펜세트
8000
25
3세트
5
3종 케이스세트
18000
9
1박스
6
파우치
15000
14
2개
7
보관함
22000
7
1박스
8
여행키트
27000
11
2세트
9
동작 원리
두 MATCH 함수가 세로 목록의 행 번호와 가로 머리글의 열 번호를 찾고 INDEX 함수가 두 위치가 만나는 값을 반환합니다.
INDEX/MATCH 양방향 검색 예제 시트에서 H2 셀이 1박스를 반환하는 계산 과정입니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.
1
첫 번째 MATCH 함수가 행 번호를 찾습니다
MATCH 함수는 A2:A8에서 3종 케이스세트를 정확히 검색합니다. 해당 제품은 범위의 네 번째 위치에 있으므로 행 번호 4를 반환합니다.
=MATCH ( F2, $A$2:$A$8, 0 )
F2의 제품명을 정확히 일치로 찾습니다. = 4
A2:A8에서 3종 케이스세트의 상대 위치입니다. 2
두 번째 MATCH 함수가 열 번호를 찾습니다
MATCH 함수는 A1:D1에서 최소주문수량 머리글을 정확히 검색합니다. 해당 머리글은 범위의 네 번째 위치에 있으므로 열 번호 4를 반환합니다.
=MATCH ( G2, $A$1:$D$1, 0 )
G2의 머리글을 정확히 일치로 찾습니다. = 4
A1:D1에서 최소주문수량의 상대 위치입니다. 3
INDEX 함수가 두 위치의 교차값을 반환합니다
INDEX 함수는 A2:D8에서 네 번째 행과 네 번째 열이 만나는 값을 찾습니다. 두 위치가 만나는 값은 1박스입니다.
=INDEX ( $A$2:$D$8, 4, 4 )
두 MATCH 함수가 반환한 행 번호와 열 번호를 사용합니다. = "1박스"
3종 케이스세트의 최소주문수량입니다. 02중첩 XLOOKUP 대체형 (Excel 2021 이상)
=XLOOKUP ( 가로값, 가로범위, XLOOKUP ( 세로값, 세로범위, 전체범위 ) )인수 설명 자세히 보기
인수구분설명
가로값필수가로범위에서 검색할 머리글입니다.
가로범위필수가로값의 위치를 찾을 머리글 범위입니다.
세로값필수세로범위에서 검색할 값입니다.
세로범위필수세로값의 위치를 찾을 단일 열 범위입니다.
전체범위필수세로값과 같은 행 전체를 먼저 반환할 데이터 범위입니다. 가로범위와 열 개수가 같아야 합니다.
A
B
C
D
E
F
G
H
I
1
제품명
단가
재고
최소주문수량
찾을 제품
찾을 항목
검색 결과
2
노트세트
12000
18
2박스
3종 케이스세트
최소주문수량
1박스
3
메모패드
5000
32
5권
4
펜세트
8000
25
3세트
5
3종 케이스세트
18000
9
1박스
6
파우치
15000
14
2개
7
보관함
22000
7
1박스
8
여행키트
27000
11
2세트
9
동작 원리
안쪽 XLOOKUP 함수가 세로값과 같은 행을 반환하고 바깥쪽 XLOOKUP 함수가 그 행에서 가로값의 열을 선택합니다.
중첩 XLOOKUP 대체형 예제 시트에서 H2 셀이 1박스를 반환하는 계산 과정입니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.
1
안쪽 XLOOKUP 함수가 제품 행을 반환합니다
안쪽 XLOOKUP 함수는 A2:A8에서 3종 케이스세트를 찾아 A2:D8의 같은 행 전체를 가로 배열로 반환합니다.
=XLOOKUP ( F2, $A$2:$A$8, $A$2:$D$8 )
3종 케이스세트와 같은 행의 네 값을 가져옵니다. = {"3종 케이스세트",18000,9,"1박스"}
A5:D5의 네 값으로 이루어진 가로 배열입니다. 2
바깥쪽 XLOOKUP 함수가 머리글 열을 선택합니다
바깥쪽 XLOOKUP 함수는 A1:D1에서 최소주문수량을 찾아 안쪽 함수가 반환한 배열의 네 번째 값인 1박스를 반환합니다.
=XLOOKUP ( G2, $A$1:$D$1, XLOOKUP ( F2, $A$2:$A$8, $A$2:$D$8 ) )
최소주문수량 머리글과 같은 위치의 값을 선택합니다. = "1박스"
3종 케이스세트의 최소주문수량입니다.
이 공식이 사용하는 함수
궁금한 점이 있어 댓글 남깁니다. 실무에서 index-match함수를 가끔 사용하는데 2가지 조건하에서는 잘 활용하는데 3가지 이상의 조건에서는 항상 에러가 나더라고요. 예를 들면 '연도별&월별&판매처'의 수량데이터를 불러온다던지 이런 경우에는 두 가지 조건(match(연도별&월별,~),match(판매처,~)으로 정리하곤 합니다. 해서, 3가지 이상의 다중조건의 경우 어떤 식으로 적용하는게 좋은지 궁금합니다.
MATCH 함수를 아래와 같이 사용해보세요.
MATCH(1,( --(조건범위=조건)*--(조건범위=조건)*...),0)
감사합니다!
만약 #VALUE! 오류가 출력되신다면, 함수를 Ctrl + Shift + Enter로 배열수식으로 입력해보시겠어요?
감사합니다!
저도 2개 조건은 쉽게 되는데..2개 이상의 조건(특히 행열 조건을 같이 줘야 할때..잘안되서..
조건 앞에 --를 붙여 보라는 말씀 이신지?
원리는 INDEX(범위,행번호,열번호) 에서 각 행번호와 열번호를 MATCH 함수로 입력하는 것입니다. 각 구간별로 나눠 공부하시면 쉽게 이해할 수 있으실 거에요.
화이팅입니다!(ง •̀_•́)ง