INDEX-MATCH로 여러 조건 조회하기

때로는 하나의 열만으로 행을 고유하게 식별할 수 없습니다. 특정 사이즈의 제품 가격이나 특정 부서의 직원 급여를 찾아야 할 수 있습니다.

이럴 때는 정확히 한 행을 찾기 위해 두 개 이상의 열을 동시에 기준으로 일치시키는 다중 조건 조회가 필요합니다.

INDEX-MATCH는 조건을 하나의 일치 테스트로 결합하여 이 작업을 우아하게 처리하며, 별도의 보조 열도 필요하지 않습니다.

가장 단순한 개념은 키 열들을 하나로 합치는 것입니다. 제품과 사이즈를 이어 붙이는 보조 열을 추가한 다음, 그 열을 대상으로 일반적인 조회를 수행합니다.

예를 들어 보조 셀에 =A2&"|"&B2를 입력하면 "Shirt|Large"가 만들어집니다. 그런 다음 결합된 열에서 "Shirt|Large"를 MATCH로 찾습니다.

이 방법은 작동하지만 시트가 복잡해집니다. 다음 장면부터는 보조 열을 완전히 생략하는 방법을 살펴봅니다.

핵심 기법은 MATCH 안에서 두 조건 테스트를 곱하는 것입니다.

(A2:A10=G1)은 첫 번째 기준에 대해 TRUE/FALSE 배열을 만듭니다. (B2:B10=G2)도 두 번째 기준에 대해 같은 작업을 합니다. 두 배열을 (A2:A10=G1)*(B2:B10=G2)처럼 곱하면 두 조건이 모두 TRUE인 위치에서만 1이 되고 나머지는 0이 됩니다.

그런 다음 MATCH가 값 1을 찾아 두 조건을 모두 충족하는 행을 찾습니다.

스프레드시트에서 TRUE는 1로, FALSE는 0으로 동작합니다. 이 둘을 곱하면 논리적인 AND를 흉내 낼 수 있습니다.

• 1 곱하기 1 = 1(두 조건 모두 충족)
• 1 곱하기 0 = 0
• 0 곱하기 1 = 0
• 0 곱하기 0 = 0

따라서 두 기준이 모두 충족되는 행만 1을 생성합니다. 나머지 모든 행은 0이 됩니다. 이 하나의 1이 원하는 행을 표시합니다.

이제 곱한 배열을 MATCH로 감싸 정확한 값 1을 검색합니다.

MATCH(1, (A2:A10=G1)*(B2:B10=G2), 0)은 두 조건이 모두 TRUE인 첫 번째 행의 위치를 반환합니다.

일치하는 조합이 네 번째 데이터 행에 있다면 MATCH는 4를 반환합니다. 이 위치를 INDEX가 사용하여 결과를 가져옵니다.

그 MATCH 결과를 원하는 열, 예를 들어 C2:C10의 가격 열에 적용합니다.

완성된 수식은 다음과 같이 해석할 수 있습니다. C2:C10에서 제품이 G1과 같고 동시에 사이즈가 G2와 같은 행의 값을 반환합니다.

이는 보조 열이나 데이터 재배열 없이 수행하는 진정한 다중 조건 조회입니다.

이 수식은 조건 배열을 계산합니다. 최신 Excel과 Google 스프레드시트에서는 Enter 키만 누르면 작동합니다.

이전 버전의 Excel(동적 배열이 도입되기 전)에서는 Ctrl+Shift+Enter로 배열 수식임을 확인해야 하며, 그러면 중괄호가 추가됩니다. 이전 버전의 Excel에서 결과가 잘못되거나 오류가 표시된다면 대개 이 확인 단계가 누락된 것입니다.

기준이 세 개인가요? 다른 테스트를 하나 더 곱하면 됩니다. 예를 들어 D열의 색상도 입력값 G3과 일치시키고 싶다고 가정해 보겠습니다.

각각의 추가 (range=criterion) 요소가 결과의 범위를 더 좁힙니다. 모든 조건이 TRUE인 행만 곱셈 결과 1을 유지하고, 하나라도 FALSE이면 전체 곱셈 결과가 0이 됩니다.

필요한 열의 수만큼 이 패턴을 확장할 수 있습니다.

데이터: A = 제품, B = 사이즈, C = 가격입니다. "Large" 사이즈의 "Shirt" 가격을 구하려고 합니다.

• G1 = "Shirt", G2 = "Large"입니다.
• 조건 배열은 Shirt와 Large가 모두 일치하는 행, 예를 들어 4행에서만 1을 생성합니다.
• MATCH(1, ..., 0)은 4를 반환합니다.
• INDEX(C2:C10, 4)는 해당 행의 가격을 반환합니다.

어느 입력값이든 바꾸면 수식이 즉시 올바른 행을 다시 찾습니다.

다음 사항을 기억하세요.

• 동일한 범위: 모든 조건 범위와 INDEX 열의 높이가 같아야 합니다.
• 일치 항목 없음: 모든 기준을 충족하는 행이 없으면 MATCH가 #N/A를 반환합니다. 전체 수식을 IFERROR로 감싸세요.
• 중복: 일치하는 행이 여러 개면 MATCH는 첫 번째 행만 반환합니다. 고유한 결과를 얻을 수 있도록 기준을 충분히 구체적으로 지정하세요.

여러 행이 일치하고 그중 하나를 가져오기보다 값을 합계 내고 싶다면, SUMPRODUCT는 배열 수식으로 입력하는 INDEX-MATCH의 깔끔한 대안입니다.

조건 배열에 값 열을 곱한 뒤 결과를 더하므로, 두 기준을 충족하는 행만 계산에 포함됩니다. SUMPRODUCT가 배열을 기본적으로 처리하므로 Ctrl+Shift+Enter도 필요하지 않습니다.

하나의 일치 값을 가져올 때는 INDEX-MATCH를 사용하고, 모든 일치 항목을 집계할 때는 SUMPRODUCT를 사용하세요.

INDEX-MATCH를 사용한 다중 조건 조회의 경우:

• 조건 배열을 서로 곱합니다. (A=G1)*(B=G2)는 모든 조건이 충족되는 위치에서만 1이 됩니다(논리적 AND).
• MATCH(1, ..., 0)은 해당 행의 위치를 찾습니다.
• INDEX(returnCol, position)은 값을 반환합니다.

조건을 추가하려면 *(range=criterion) 요소를 더하고, 범위의 높이를 같게 유지하세요. Excel의 이전 버전에서는 Ctrl+Shift+Enter로 확정하고, IFERROR로 오류에 대비하세요.