=XLOOKUP(1,(A2:A7=E2)*(B2:B7=F2),C2:C7)은 상품이 E2이고 크기가 F2인 행의 가격을 반환합니다. 각 비교식이 모든 행을 검사하고, 둘을 곱하면 둘 다 참인 곳에서만 1이 되며, XLOOKUP이 그 1을 찾습니다. Excel 2021이나 Microsoft 365가 필요하며, 아래의 INDEX MATCH 버전은 모든 버전에서 동작합니다.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Size | Price | Product | Size | Price | |
| 2 | Coffee | Small | $2.50 | Tea | Large | $3.00 | |
| 3 | Coffee | Large | $3.50 | ||||
| 4 | Tea | Small | $2.00 | ||||
| 5 | Tea | Large | $3.00 | ||||
| 6 | Juice | Small | $3.00 | ||||
| 7 | Juice | Large | $4.00 |
Tea와 Large는 5행에서 만나므로 G2는 $3.00을 반환합니다. Juice와 Small을 고르면 다른 행에서 다시 $3.00이 나옵니다. 두 조건에 모두 맞는 행이 없는 경우를 위해 네 번째 인수를 추가하세요: =XLOOKUP(1,(A2:A7=E2)*(B2:B7=F2),C2:C7,"No such item").
곱한 조건이 동작하는 방식
A2:A7=E2는 모든 상품을 E2와 비교해 TRUE나 FALSE 값 여섯 개를 반환합니다. 이런 목록 두 개를 곱하면 TRUE는 1, FALSE는 0이 되고, 두 목록 모두에서 1인 행만 1이 됩니다. D열은 수식 하나에서 분산된 그 목록을 보여 줍니다.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Size | Price | Both match | Product | Size | |
| 2 | Coffee | Small | $2.50 | 0 | Tea | Large | |
| 3 | Coffee | Large | $3.50 | 0 | |||
| 4 | Tea | Small | $2.00 | 0 | |||
| 5 | Tea | Large | $3.00 | 1 | |||
| 6 | Juice | Small | $3.00 | 0 | |||
| 7 | Juice | Large | $4.00 | 0 |
D5만 1입니다. F2나 G2를 바꾸면 1이 옮겨 갑니다. 조건이 하나 늘 때마다 *(range=value)를 하나 더 붙이면 되고, 조건이 같음일 필요도 없습니다. *(C2:C7<3)은 "가격 3 미만"을 추가합니다. 모든 범위는 같은 행(A2:A7, B2:B7, C2:C7)을 덮어야 합니다. 반환 범위의 크기가 조건과 다르면 XLOOKUP은 #VALUE!를 반환합니다.
여러 조건을 쓰는 INDEX MATCH
Excel 2019 이하에서는 MATCH가 같은 배열에서 1을 찾고, INDEX가 그 위치의 가격을 반환할 수 있습니다.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Size | Price | Product | Size | Price | |
| 2 | Coffee | Small | $2.50 | Coffee | Large | $3.50 | |
| 3 | Coffee | Large | $3.50 | ||||
| 4 | Tea | Small | $2.00 | ||||
| 5 | Tea | Large | $3.00 | ||||
| 6 | Juice | Small | $3.00 | ||||
| 7 | Juice | Large | $4.00 |
Coffee와 Large는 배열의 2번 위치이므로 INDEX는 $3.50을 반환합니다. Excel 2019 이하에서는 이것이 배열 수식입니다. Enter 대신 Ctrl+Shift+Enter(Mac에서는 Cmd+Shift+Enter)를 누르면 엑셀이 수식을 중괄호로 감싸 보여 줍니다. 그곳에서 그냥 Enter를 누르면 보통 #N/A나 #VALUE!가 나옵니다. Excel 365에서는 Enter로 충분합니다. 조건이 하나인 형태는 INDEX와 MATCH 페이지에 있습니다.
조건을 하나의 키로 합치기
다른 방법은 조건 두 개를 합쳐 하나로 만드는 것입니다. VLOOKUP은 합친 값을 표 맨 앞의 도우미 열에 두어야 합니다(그 버전은 VLOOKUP 페이지에서 보여 줍니다). XLOOKUP은 수식 안에서 범위를 합칠 수 있으므로 도우미 열이 필요 없습니다.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Size | Price | Product | Size | Price | |
| 2 | Coffee | Small | $2.50 | Juice | Large | $4.00 | |
| 3 | Coffee | Large | $3.50 | ||||
| 4 | Tea | Small | $2.00 | ||||
| 5 | Tea | Large | $3.00 | ||||
| 6 | Juice | Small | $3.00 | ||||
| 7 | Juice | Large | $4.00 |
A2:A7&"|"&B2:B7은 Juice|Large 같은 키 여섯 개를 만들고, XLOOKUP이 그중에서 Juice|Large를 찾아 $4.00을 냅니다. 부분 사이에는 구분 기호를 넣으세요. 구분 기호가 없으면 "AB"와 "C"가 "A"와 "BC"를 합친 것과 같은 "ABC"가 되어, 조회가 엉뚱한 행을 반환할 수 있습니다.
원하는 값이 숫자이고 각 조합이 한 번씩만 나온다면, SUMIFS가 배열 없이 같은 답을 줍니다: =SUMIFS(C2:C7,A2:A7,E2,B2:B7,F2). 일치하는 값이 없으면 오류 대신 0을 반환하므로 오타가 가려질 수 있습니다.
FILTER로 일치하는 행 모두 가져오기
XLOOKUP과 INDEX MATCH는 처음으로 일치하는 행을 반환합니다. 여러 행이 일치하고 그 전부를 원한다면 같은 조건으로 FILTER를 쓰세요.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Product | Quarter | Sales | Matches | ||
| 2 | North | Laptop | Q1 | 120 | Q1 | 210 | |
| 3 | North | Phone | Q1 | 210 | Q2 | 225 | |
| 4 | South | Laptop | Q1 | 95 | Q3 | 240 | |
| 5 | North | Phone | Q2 | 225 | |||
| 6 | South | Phone | Q2 | 180 | |||
| 7 | North | Laptop | Q2 | 135 | |||
| 8 | North | Phone | Q3 | 240 |
North이면서 Phone인 행이 세 개이므로 F2가 그 분기와 매출을 F2:G4로 분산합니다. A3을 South로 바꾸면 목록이 두 개로 줄어듭니다. 일치하는 행이 없으면 FILTER는 #CALC!를 반환하며, 세 번째 인수로 "None" 같은 값을 주면 대신 텍스트를 보여 줍니다. 다른 옵션은 FILTER 페이지에 있습니다.
연습: 조건 세 개
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Product | Quarter | Sales | Region | South | |
| 2 | North | Laptop | Q1 | 120 | Product | Laptop | |
| 3 | North | Laptop | Q2 | 135 | Quarter | Q2 | |
| 4 | North | Phone | Q1 | 210 | Sales | ||
| 5 | South | Laptop | Q1 | 95 | |||
| 6 | South | Phone | Q2 | 180 | |||
| 7 | South | Laptop | Q2 | 110 | |||
| 8 | North | Phone | Q2 | 225 |
직접 해 보세요: G4에 G1의 지역, G2의 상품, G3의 분기에 해당하는 매출을 반환하세요.
자주 묻는 질문
XLOOKUP을 여러 조건으로 쓰려면 어떻게 하나요?
조건마다 비교식을 하나씩 곱하고 1을 찾습니다: =XLOOKUP(1,(A2:A7=E2)*(B2:B7=F2),C2:C7). 각 비교식은 행마다 TRUE나 FALSE를 내고, 곱은 모두 TRUE인 곳에서만 1이 되며, XLOOKUP이 그런 첫 행을 반환합니다.
조건이 두 개인 INDEX MATCH는 어떻게 쓰나요?
MATCH 안에 같은 곱한 조건을 씁니다: =INDEX(C2:C7,MATCH(1,(A2:A7=E2)*(B2:B7=F2),0)). Excel 2019 이하에서는 Ctrl+Shift+Enter(Mac에서는 Cmd+Shift+Enter)로 확정하세요.
VLOOKUP에 조건을 두 개 쓸 수 있나요?
직접은 안 됩니다. 표 맨 앞에 두 값을 합치는 도우미 열(예: =A2&"|"&B2)을 추가하고, 합친 값을 조회하세요: =VLOOKUP(E2&"|"&F2,helper_table,col,FALSE).
SUMIFS로 조건 두 개짜리 조회를 대신할 수 있나요?
값이 숫자이고 각 조합이 한 번씩만 나온다면 됩니다: =SUMIFS(C2:C7,A2:A7,E2,B2:B7,F2). 일치하는 행이 없으면 #N/A 대신 0을 반환하고, 조합이 두 번 나오면 값을 더합니다.
OR 조건으로 조회하려면 어떻게 하나요?
조건을 곱하는 대신 더합니다. (A2:A7="Tea")+(A2:A7="Juice")는 둘 중 하나라도 참인 곳에서 1 이상입니다. 0보다 큰 값을 조회하세요. 예를 들어 =XLOOKUP(TRUE,((A2:A7="Tea")+(A2:A7="Juice"))>0,C2:C7)입니다.
