엑셀 INDEX MATCH 다중 조건과 2차원 찾기

핵심 요약

행 머리글과 열 머리글이 만나는 값은 =INDEX(표, MATCH(행 조건, 행 머리글, 0), MATCH(열 조건, 열 머리글, 0))로 찾습니다.
여러 조건이 모두 맞는 행은 MATCH(조건1&조건2, 범위1&범위2, 0) 형태의 배열 수식으로 찾습니다.
배열 수식은 엑셀 2019 이하에서 Ctrl+Shift+Enter로 입력하고, 마이크로소프트 365·엑셀 2021 이후에서는 Enter만 눌러도 됩니다.

INDEX MATCH 기본형은 한 열에서 값을 찾아 다른 열의 값을 가져옵니다. 그런데 실무 표는 "3월의 매출"처럼 행과 열을 함께 골라야 하거나, "2월에 팔린 브라우니"처럼 조건이 두 개 이상인 경우가 많습니다.

이 글에서는 MATCH를 두 번 써서 표의 교차 지점을 찾는 방법과, 여러 조건으로 행을 찾는 배열 수식을 정리합니다. 버전에 따라 입력 방법이 다른 부분과 조건을 붙일 때 생기는 함정도 함께 다룹니다.

INDEX에 행 번호와 열 번호를 함께 넣기

INDEX는 한 열짜리 범위뿐 아니라 여러 행·여러 열로 된 표 전체를 범위로 받을 수 있습니다. 이때는 행 번호와 열 번호를 모두 넣습니다.

=INDEX(표 범위, 행 번호, 열 번호)

인수	뜻	예시
표 범위	머리글을 포함하거나 제외한 표 전체	`B2:D7`
행 번호	표 범위의 첫 행을 1로 보고 몇 번째 행인지	`4`
열 번호	표 범위의 첫 열을 1로 보고 몇 번째 열인지	`2`

Month, Cookie packs sold, Revenue 열로 된 B2:D7 표 옆 F2 셀에 INDEX(B2:D7, 4, 2) 수식을 넣어 19가 표시된 엑셀 화면 — B2:D7의 네 번째 행, 두 번째 열 값을 가져온 결과 (영문판 엑셀 화면 · 이미지 출처: Deskbright's Ultimate Excel Book, 저자 허락 하에 사용)

B2:D7은 머리글 행부터 시작하므로 네 번째 행은 March, 두 번째 열은 Cookie packs sold입니다. 두 위치가 만나는 칸의 값 19가 결과입니다.

MATCH 두 번으로 행과 열 동시에 찾기

행 번호와 열 번호를 숫자로 적는 대신 각각 MATCH로 계산하면, 월 이름과 항목 이름만 바꿔도 원하는 값을 찾는 조회표가 됩니다. 흔히 INDEX MATCH MATCH라고 부르는 형태입니다.

G2 셀에 월(March), G3 셀에 항목(Revenue)을 입력합니다.
결과 셀에 =INDEX(B2:D7, MATCH(G2, B2:B7, 0), MATCH(G3, B2:D2, 0))를 입력합니다.
첫 번째 MATCH는 B2:B7에서 March의 위치 4를, 두 번째 MATCH는 B2:D2에서 Revenue의 위치 3을 구합니다.
INDEX가 표의 4행·3열 값인 $456을 돌려줍니다.

G2에 March, G3에 Revenue를 입력하고 INDEX(B2:D7, MATCH(G2,B2:B7,0), MATCH(G3,B2:D2,0)) 수식을 입력하는 엑셀 화면 — 월과 항목을 입력 셀로 받아 교차 값을 찾는 수식 (영문판 엑셀 화면 · 이미지 출처: Deskbright's Ultimate Excel Book, 저자 허락 하에 사용)

세 범위의 시작 위치가 맞아야 합니다. 행을 찾는 범위(B2:B7)는 표 범위와 같은 행에서 시작하고 행 수가 같아야 하며, 열을 찾는 범위(B2:D2)는 표 범위와 같은 열에서 시작하고 열 수가 같아야 합니다. 표 범위를 B3:D7로 바꿨다면 행을 찾는 범위도 B3:B7로 함께 바꿔야 합니다.

여러 조건으로 찾기: &로 조건 붙이기

한 행에 "월"과 "품목"이 따로 적힌 목록에서 2월 브라우니 판매량처럼 조건 두 개가 모두 맞는 행을 찾으려면, 찾을 값과 찾을 범위를 각각 &로 이어 붙입니다.

=MATCH("February"&"Brownies", B3:B8&C3:C8, 0)

B3:B8&C3:C8은 각 행의 월과 품목을 붙인 목록(JanuaryCookies, JanuaryBrownies, FebruaryCookies …)을 한꺼번에 만듭니다. 그 목록에서 FebruaryBrownies가 네 번째에 있으므로 결과는 4입니다. 조건의 순서와 범위의 순서는 같아야 합니다.

Month, Item, Units sold 열로 된 목록에서 MATCH(February&Brownies, B3:B8&C3:C8, 0) 수식을 입력하는 엑셀 화면 — 월 열과 품목 열을 이어 붙여 두 조건을 한 번에 찾습니다 (영문판 엑셀 화면 · 이미지 출처: Deskbright's Ultimate Excel Book, 저자 허락 하에 사용)

이제 이 MATCH를 INDEX에 넣으면 됩니다. G2에 월, G3에 품목을 입력해 두고 다음 수식을 씁니다.

=INDEX(D3:D8, MATCH(G2&G3, B3:B8&C3:C8, 0))

G2에 March, G3에 Cookies를 입력하고 G4에 중괄호로 감싼 INDEX MATCH 배열 수식을 넣어 29가 표시된 엑셀 화면 — 수식 입력줄의 중괄호는 구버전에서 배열 수식으로 입력됐다는 표시입니다 (영문판 엑셀 화면 · 이미지 출처: Deskbright's Ultimate Excel Book, 저자 허락 하에 사용)

March와 Cookies가 함께 있는 행의 판매량 29가 나옵니다. 조건이 세 개 이상이면 G2&G3&G4, B3:B8&C3:C8&D3:D8처럼 계속 이어 붙입니다.

버전별 입력 방법

버전	입력 방법	표시
엑셀 2019 이하	`Ctrl`+`Shift`+`Enter`	수식 입력줄에 `{ }`가 붙음
마이크로소프트 365, 엑셀 2021 이후	`Enter`	중괄호 없이 그대로 계산

구버전에서 Enter만 누르면 #N/A나 #VALUE!가 나오거나 엉뚱한 결과가 나올 수 있습니다.

조건을 이어 붙일 때 주의할 점

우연히 같은 문자열이 될 수 있습니다. 코드 "1"과 "23"을 붙인 값과 "12"와 "3"을 붙인 값은 둘 다 "123"입니다. 이런 데이터라면 G2&"|"&G3, B3:B8&"|"&C3:C8처럼 사이에 구분 문자를 넣습니다.
숫자 조건은 텍스트로 붙습니다. "Brownies"&76은 "Brownies76"이 되며, 범위 쪽도 똑같이 붙기 때문에 정상적으로 찾아집니다. 다만 표시 형식만 다르고 실제 값이 다른 숫자는 일치하지 않을 수 있습니다.

구분 문자 대신 조건을 곱하는 방식도 있습니다. =INDEX(D3:D8, MATCH(1, (B3:B8=G2)*(C3:C8=G3), 0))는 두 조건이 모두 참인 행만 1이 되는 성질을 이용하며, 붙인 문자열이 겹칠 걱정이 없습니다. 이 수식도 구버전에서는 Ctrl+Shift+Enter로 입력합니다. 마이크로소프트 365·엑셀 2021 이후라면 =XLOOKUP(1, (B3:B8=G2)*(C3:C8=G3), D3:D8)로 더 짧게 쓸 수 있습니다.

구글 스프레드시트에서는

구글 스프레드시트에서도 INDEX MATCH MATCH는 엑셀과 같은 수식으로 동작합니다. 여러 조건을 붙이는 배열 계산은 ARRAYFORMULA로 감싸 =ARRAYFORMULA(INDEX(D3:D8, MATCH(G2&G3, B3:B8&C3:C8, 0)))처럼 쓰는 것이 안전합니다. 수식을 편집하는 중에 Ctrl+Shift+Enter를 누르면 ARRAYFORMULA가 자동으로 붙습니다.

자주 묻는 질문

중괄호 { }를 직접 입력하면 안 되나요?

안 됩니다. 중괄호를 타이핑하면 엑셀이 수식이 아닌 텍스트로 받아들입니다. 엑셀 2019 이하에서는 수식을 입력한 뒤 Ctrl+Shift+Enter를 눌러야 중괄호가 자동으로 붙습니다.

조건에 맞는 행이 여러 개면 어떻게 되나요?

MATCH는 조건을 만족하는 첫 번째 행의 위치만 돌려주므로 첫 번째 값만 나옵니다. 조건에 맞는 값의 합계가 필요하다면 SUMIFS가 더 알맞습니다.

배열 수식을 고친 뒤 결과가 이상해졌습니다.

엑셀 2019 이하에서는 배열 수식을 수정한 뒤에도 Enter가 아니라 Ctrl+Shift+Enter로 다시 확정해야 합니다. 수식 입력줄에 중괄호가 사라졌다면 다시 입력하세요.