엑셀 고급 조회: 다중 조건 조회와 근사 일치

단순 조회는 열 하나로 값을 찾아요. 제품의 가격, 직원의 부서 같은 것들이죠. 하지만 실제 요율표는 그렇게 단순한 경우가 드물어요. 수수료율이 지역과 구분에 따라 달라지기도 하고, 보너스 등급은 매출이 정확히 얼마인지가 아니라 어느 구간에 드는지에 따라 정해지기도 해요. 이 글에서는 두 경우를 모두 해결하는 패턴을 배워요. 여러 조건으로 조회하기, 행과 열로 이루어진 격자 표에서 조회하기, 그리고 근사 일치(approximate match)로 등급 정하기예요.

예제는 영업팀 데이터예요. 담당자마다 지역과 구분이 있고, 지역과 구분의 조합별 수수료율은 F1:H7의 요율표에 있어요.

	A	B	C	D	F	G	H
1	담당자	지역	구분	요율	지역	구분	요율
2	이아리	동부	B		동부	A	4%
3	박미나	서부	A		서부	B	7%
4	배준호	중부	B		중부	A	3%
5	오지은	동부	A		동부	B	6%
6	신동현	서부	B		서부	A	5%
7					중부	B	5%

A1:C6에는 담당자 다섯 명과 비어 있는 요율 열이, F1:H7에는 지역과 구분의 조합마다 한 행씩 있는 요율표가 있어요.

여기서는 지역만으로 XLOOKUP을 하면 틀린 답이 나와요. 이아리는 동부, 구분 B인데, 요율표에서 처음 나오는 동부 행은 동부 A예요. 그래서 =XLOOKUP(B2,$F$2:$F$7,$H$2:$H$7)은 6%가 아니라 4%를 돌려줘요. 두 열을 동시에 일치시켜야 해요.

도우미 키 열로 두 열 일치시키기

가장 오래되고 어느 버전에서나 통하는 방법은 조회하는 쪽과 조회 대상 쪽 모두에서 조건들을 키 하나로 이어 붙이는 거예요. 요율표 쪽의 키는 도우미 열(helper column)에 만들어 둬요.

요율표에 키 열을 추가하세요
요율표 왼쪽의 E2를 클릭하고 다음 수식을 입력하세요.
=F2&"-"&G2
& 연산자는 텍스트를 이어 붙여요. 그래서 E2에는 동부-A가 표시돼요. 수식을 E7까지 채우고 E1에 키를 입력하세요.

조회할 때도 같은 키를 만드세요

D2를 클릭하고 담당자의 지역과 구분을 같은 방식으로 이어 붙인 다음, 그 키를 새 열에서 찾으세요.

=XLOOKUP(B2&"-"&C2,$E$2:$E$7,$H$2:$H$7)

B2&"-"&C2는 동부-B가 되고, 이 키는 E5에 있어요. 그래서 D2에는 6%가 표시돼요.

	A	B	C	D	E	F	G	H
1	담당자	지역	구분	요율	키	지역	구분	요율
2	이아리	동부	B	6%	동부-A	동부	A	4%
3	박미나	서부	A		서부-B	서부	B	7%
4	배준호	중부	B		중부-A	중부	A	3%
5	오지은	동부	A		동부-B	동부	B	6%
6	신동현	서부	B		서부-A	서부	A	5%
7					중부-B	중부	B	5%

도우미 열 E에서 지역과 구분을 이어 붙였어요. 조회 수식도 같은 키인 동부-B를 만들어 E5에서 찾아요.

아래로 채우세요
D2를 D6까지 복사하세요. $ 기호 덕분에 요율표는 고정되고, B2와 C2는 행마다 따라 움직여요. 박미나는 5%, 배준호는 5%, 오지은은 4%, 신동현은 7%가 나와요.

두 가지를 꼭 지키세요. 첫째, - 같은 구분 기호를 넣으세요. 구분 기호가 없으면 서로 다른 조합이 같은 키가 될 수 있어요. "AB"&"C"와 "A"&"BC"는 둘 다 ABC예요. 둘째, 양쪽 키를 똑같은 순서와 구분 기호로 만드세요.

키 열이 E:H의 첫 번째 열이라서, 이 도우미 키 방식은 모든 버전의 엑셀에서 VLOOKUP으로도 쓸 수 있어요. =VLOOKUP(B2&"-"&C2,$E$2:$H$7,4,FALSE)처럼요.

도우미 열 없이 조회하기

Microsoft 365, Excel 2024, Excel 2021에서는 수식이 범위 전체를 한꺼번에 계산할 수 있어요. 그래서 키를 조회 수식 안에서 만들고 요율표는 그대로 둘 수 있어요.

수식 안에서 열을 이어 붙이기. $F$2:$F$7&"-"&$G$2:$G$7이 메모리에서 키 여섯 개를 만들고, XLOOKUP이 그 안에서 찾아요.

=XLOOKUP(B2&"-"&C2,$F$2:$F$7&"-"&$G$2:$G$7,$H$2:$H$7)

열을 추가하지 않아도 D2에 앞에서와 똑같이 6%가 표시돼요.

조건을 곱하기. 조건이 세 개 이상으로 늘어날 때 가장 편한 방법이에요. D3을 클릭하고 입력하세요.

=XLOOKUP(1,($F$2:$F$7=B3)*($G$2:$G$7=C3),$H$2:$H$7)

박미나(서부, 구분 A)의 경우 이렇게 계산돼요.

$F$2:$F$7=B3은 모든 지역을 서부와 비교해서 행마다 TRUE나 FALSE를 돌려줘요. FALSE, TRUE, FALSE, FALSE, TRUE, FALSE예요.
$G$2:$G$7=C3은 모든 구분을 A와 비교해요. TRUE, FALSE, TRUE, FALSE, TRUE, FALSE예요.
곱하면 TRUE는 1, FALSE는 0이 되기 때문에 두 조건이 모두 참인 곳만 1이 돼요. 0, 0, 0, 0, 1, 0이에요.
XLOOKUP이 1을 찾으면 다섯 번째 행에 있으니 다섯 번째 요율인 5%를 돌려줘요.

	A	B	C	D	F	G	H
1	담당자	지역	구분	요율	지역	구분	요율
2	이아리	동부	B	6%	동부	A	4%
3	박미나	서부	A	5%	서부	B	7%
4	배준호	중부	B		중부	A	3%
5	오지은	동부	A		동부	B	6%
6	신동현	서부	B		서부	A	5%
7					중부	B	5%

두 조건을 모두 통과하는 건 시트의 6행(서부, A)뿐이라서 XLOOKUP이 그 행의 요율 5%를 돌려줘요.

조건을 하나 더 추가하려면 비교식을 하나 더 곱하세요. 요율표의 I열에 세 번째 조건이 있고 담당자의 해당 값이 J3에 있다면 *($I$2:$I$7=J3)을 곱하면 돼요. 모든 조건에 맞는 행이 없으면 결과는 #N/A예요. 대신 메시지를 보여 주려면 네 번째 인수를 쓰세요. =XLOOKUP(1,($F$2:$F$7=B3)*($G$2:$G$7=C3),$H$2:$H$7,"요율 없음")처럼요.

INDEX와 MATCH로 다시 만들기

INDEX와 MATCH는 같은 일을 두 단계로 나눠서 해요. 오래된 통합 문서에서 자주 볼 수 있어요. MATCH는 목록에서 값의 위치를 돌려주고, INDEX는 그 위치에 있는 값을 돌려줘요.

배준호(중부, 구분 B)의 요율을 구하려면 D4를 클릭하고 입력하세요.

=INDEX($H$2:$H$7,MATCH(B4&"-"&C4,$F$2:$F$7&"-"&$G$2:$G$7,0))

MATCH가 중부-B를 여섯 번째 위치에서 찾고, INDEX가 여섯 번째 요율인 5%를 돌려줘요. MATCH 끝의 0은 정확히 일치라는 뜻이에요. 꼭 입력하세요. MATCH의 기본값은 아래에서 다룰 근사 일치예요.

곱한 조건도 똑같이 쓸 수 있어요. =INDEX($H$2:$H$7,MATCH(1,($F$2:$F$7=B4)*($G$2:$G$7=C4),0))처럼요.

XMATCH는 MATCH의 새 버전이에요. 기본값이 정확히 일치라서 =XMATCH("서부",$F$2:$F$7)은 처음 나오는 서부 행의 위치인 2를 돌려줘요. INDEX와 XMATCH를 함께 쓰는 방법도 INDEX와 MATCH를 함께 쓰는 방법과 같아요.

격자 표에서 행과 열로 조회하기

요율표는 목록이 아니라 격자 형태인 경우도 많아요. 지역은 왼쪽에 세로로, 구분은 위쪽에 가로로 놓인 표예요. 이런 표를 읽으려면 행과 열을 따로 일치시키세요.

	A	B	C	E	F	G
1	지역	A	B	지역	구분	요율
2	동부	4%	6%	중부	B
3	서부	5%	7%	동부	A
4	중부	3%	5%

같은 요율을 격자로 정리한 표예요. 지역은 A2:A4, 구분 A와 B는 B1:C1, 요율은 B2:C4에 있어요. G2와 G3에는 옆에 있는 지역과 구분의 요율이 들어가야 해요.

INDEX를 쓸 때는 격자 전체를 주고, 행 번호와 열 번호를 각각 MATCH로 찾아 넣으세요. G2를 클릭하고 입력하세요.

=INDEX($B$2:$C$4,MATCH(E2,$A$2:$A$4,0),MATCH(F2,$B$1:$C$1,0))

중부는 격자의 3행, B는 2열이라서 G2에는 C4의 값인 5%가 표시돼요.

XLOOKUP을 쓸 때는 XLOOKUP 안에 XLOOKUP을 넣으세요. 안쪽 XLOOKUP이 구분 머리글을 찾아 그 구분의 열 전체를 돌려주고, 바깥쪽 XLOOKUP이 지역을 찾아 그 열에서 알맞은 행을 골라요. G3을 클릭하고 입력하세요.

=XLOOKUP(E3,$A$2:$A$4,XLOOKUP(F3,$B$1:$C$1,$B$2:$C$4))

G3에는 동부, 구분 A의 요율인 4%가 표시돼요.

	A	B	C	E	F	G
1	지역	A	B	지역	구분	요율
2	동부	4%	6%	중부	B	5%
3	서부	5%	7%	동부	A	4%
4	중부	3%	5%

G2는 중부와 B가 만나는 C4(5%)를, G3은 동부와 A가 만나는 B2(4%)를 찾아요.

근사 일치로 숫자를 등급에 배정하기

등급표에는 각 구간의 최솟값을 적어요. 숫자를 배정할 때 필요한 건 정확히 일치하는 값이 아니라, 그 숫자가 넘어선 마지막 기준값이에요.

최소 매출	등급
0	브론즈
15,000	실버
25,000	골드

0부터 14,999.99까지는 브론즈, 15,000부터 24,999.99까지는 실버, 25,000 이상은 골드예요. 연습 시트에서는 이 표가 F1:G4에, 담당자의 매출이 B열에 있어요.

일치 모드 -1을 쓰는 XLOOKUP은 "정확히 일치하는 값, 없으면 바로 아래 값"이라는 뜻이에요. B2에 있는 이아리의 매출 18,400의 경우:

=XLOOKUP(B2,$F$2:$F$4,$G$2:$G$4,,-1)

목록에 18,400이 없으니 XLOOKUP이 바로 아래 기준값인 15,000을 골라 실버를 돌려줘요. 쉼표 두 개는 네 번째 인수인 if_not_found를 비워 둔다는 뜻이에요. XLOOKUP의 일치 모드는 기준값이 정렬되어 있지 않아도 작동해요.

일치 유형 1을 쓰는 MATCH는 찾는 값보다 작거나 같은 값 중 가장 큰 값을 찾아요. 기준값이 작은 값부터 큰 값 순서로 정렬되어 있어야 해요.

=INDEX($G$2:$G$4,MATCH(B3,$F$2:$F$4,1))

박미나의 14,600은 아직 15,000에 못 미치니까 브론즈가 나와요.

TRUE를 쓰는 VLOOKUP은 같은 일을 함수 하나로 해요. 역시 정렬된 목록이 필요해요. =VLOOKUP(B6,$F$2:$G$4,2,TRUE)는 신동현의 31,500에 대해 골드를 돌려줘요.

	A	B	C	D	F	G
1	담당자	매출	등급	단계	최소 매출	등급
2	이아리	18,400	실버		0	브론즈
3	박미나	14,600	브론즈		15,000	실버
4	배준호	22,100			25,000	골드
5	오지은	9,900
6	신동현	31,500	골드	3

F2:F4의 기준값으로 근사 일치를 했어요. 18,400은 실버, 14,600은 브론즈, 31,500은 세 번째 등급인 골드예요.

등급의 이름 대신 번호가 필요하면 같은 일치 모드로 XMATCH를 쓰세요. =XMATCH(B6,$F$2:$F$4,-1)은 골드가 세 번째 기준값이라서 신동현에 대해 3을 돌려줘요. 등급마다 보너스를 올리는 수식에 이 번호를 쓰면 편해요.

등급표를 만들 때 지킬 규칙이에요.

가능한 가장 작은 값에서 시작하세요. 첫 번째 기준값보다 작은 숫자는 들어갈 곳이 없어서 #N/A가 나와요. 0에서 시작하면 음수가 아닌 매출은 모두 배정돼요.
기준값과 정확히 같은 값은 그 등급에 속해요. 15,000은 브론즈가 아니라 실버예요.
MATCH와 VLOOKUP에는 오름차순 정렬이 필요해요. 정렬되지 않은 목록에서는 오류 없이 틀린 등급이 나와요.

자주 하는 실수

조건이 두 개 필요한 표를 조건 하나로 조회하기. 구분과 상관없이 모든 동부 담당자에게 첫 번째 동부 행의 값이 나와요. 조회할 열에 같은 값이 반복된다면 조건이 하나 더 필요해요.
양쪽 키를 다르게 만들기. B2&"-"&C2는 G2&"-"&F2나 F2&G2로 만든 키와 절대 일치하지 않아요. 양쪽에 같은 순서와 같은 구분 기호를 쓰세요.
일치 유형을 빼먹기. 0이 없는 MATCH와 FALSE가 없는 VLOOKUP은 근사 일치를 써요. 정렬되지 않은 목록에서는 그럴듯하지만 틀린 값이 나와요.
등급에 정확히 일치를 쓰기. =XLOOKUP(B2,$F$2:$F$4,$G$2:$G$4)는 18,400이 목록에 없어서 #N/A를 돌려줘요. 등급 조회에는 일치 모드 -1, 일치 유형 1, 또는 TRUE가 필요해요.
$ 기호를 빼먹기. 조회 수식을 아래로 채우면 고정하지 않은 표 범위가 한 행씩 밀려 내려가서 위쪽 행들이 검색에서 빠져요.

연습

나만의 요율 표 만들기

실제로 쓰거나 떠올릴 수 있는 2차원 요율표를 하나 고르세요. 무게 구간과 지역별 배송비, 제품과 고객 유형별 가격, 지역과 품목별 세율 등이 좋아요.
조합마다 한 행씩 있는 목록으로 정리하고, 요율이 필요한 거래 몇 건을 추가하세요.
도우미 키 열로 요율을 조회하고, XLOOKUP과 곱한 조건으로도 조회해서 두 결과가 같은지 확인하세요.
조회 수식 하나를 INDEX와 MATCH로 다시 만드세요.
같은 요율을 격자 표로 바꾸고 INDEX와 MATCH 두 개로 읽어 보세요.
주문 금액별 할인 단계 같은 등급표를 추가하고, 근사 일치로 거래마다 등급을 배정하세요.

그런 다음 아래 연습 시트 두 개를 풀어 보세요. 먼저 다중 조건 요율, 다음은 등급이에요.

직접 해 보기3개 중 0개 완료

D2에 XLOOKUP으로 이아리의 요율을 찾아보세요. 양쪽 모두 지역과 구분을 키 하나로 이어 붙여요: B2&"-"&C2를 F2:F7&"-"&G2:G7에서 찾아요.
D3에 두 조건 (F2:F7=B3)*(G2:G7=C3)을 곱하고 XLOOKUP으로 1을 찾아서 박미나의 요율을 구해 보세요.
D4에 INDEX와 MATCH로 배준호의 요율을 구해 보세요. 이어 붙인 키를 정확히 일치(일치 유형 0)로 찾아요.

점선으로 표시된 셀을 클릭하고, 입력줄에 수식을 쓴 다음 Enter를 누르세요.

	A	B	C	D	F	G	H
1	담당자	지역	구분	요율	지역	구분	요율
2	이아리	동부	B		동부	A	4%
3	박미나	서부	A		서부	B	7%
4	배준호	중부	B		중부	A	3%
5	오지은	동부	A		동부	B	6%
6	신동현	서부	B		서부	A	5%
7					중부	B	5%

직접 해 보기4개 중 0개 완료

C2에 XLOOKUP으로 이아리의 등급을 찾아보세요. 일치 모드는 -1(정확히 일치하거나 바로 아래 기준값)이에요.
C3에 INDEX와 MATCH로 박미나의 등급을 찾아보세요. 오름차순으로 정렬된 기준값에 일치 유형 1을 써요.
C6에 VLOOKUP과 근사 일치(TRUE)로 신동현의 등급을 찾아보세요.
D6에 XMATCH와 일치 모드 -1로 신동현의 등급이 몇 번째인지(브론즈 1, 골드 3) 구해 보세요.

점선으로 표시된 셀을 클릭하고, 입력줄에 수식을 쓴 다음 Enter를 누르세요.

	A	B	C	D	F	G
1	담당자	매출	등급	단계	최소 매출	등급
2	이아리	18,400			0	브론즈
3	박미나	14,600			15,000	실버
4	배준호	22,100			25,000	골드
5	오지은	9,900
6	신동현	31,500

실력 점검

도우미 열을 쓰거나 쓰지 않고 두 열을 동시에 일치시켜 값을 조회할 수 있나요?
그 조회를 INDEX와 MATCH로 다시 만들 수 있나요?
XMATCH로 목록에서 값의 위치를 찾을 수 있나요?
행과 열을 모두 일치시켜 격자 표에서 값을 가져올 수 있나요?
두 기준값 사이에 있는 숫자에 대해 등급 이름을 돌려줄 수 있나요?

도움이 되었나요? 틀린 부분을 발견하셨나요? 피드백 보내기 · admin@arumsoft.com

엑셀 고급 조회: 다중 조건 조회와 근사 일치

배울 내용

시작하기 전에

함수 연습장

예제 파일