엑셀 고급 · 대화형 레슨 12개 중 4번째
고급 조회 패턴
단순 조회는 열 하나로 값을 찾아요. 하지만 실제 커미션 표, 요율 매트릭스, 구간별 가격표는 대개 조건이 하나 이상 필요해요. 지역과 등급을 함께 보거나, 판매량이 어떤 구간 안에 드는지 봐야 하죠. 여기서 다루는 패턴으로 두 경우를 모두 해결할 수 있어요.
화면 기준: Windows용 Microsoft 365 Excel. 연습은 어느 브라우저에서나 할 수 있어요.
글로 보는 튜토리얼엑셀 고급 조회: 다중 조건 조회와 근사 일치실력 점검
먼저 해 보세요:
- 열 하나가 아니라 두 열을 동시에 일치시켜 값을 조회해 보세요.
XMATCH함수로 목록에서 값의 위치를 찾아 보세요.XLOOKUP대신INDEX와MATCH로 조회를 다시 만들어 보세요.- 정확히 일치하는 값이 아니라, 두 기준값 사이에 있는 숫자에 대해 등급이나 구간 이름을 반환해 보세요.
모두 익숙하다면 A5 — 고급 피벗 테이블 레슨으로 건너뛰세요.
개념 익히기
이번 레슨에서 배울 내용
- 다중 조건 조회
- 양쪽 모두에서 조건을 하나의 키로 이어 붙이세요.
=XLOOKUP(B2&"-"&C2,$F$2:$F$7,$H$2:$H$7)수식은 같은 방식으로 만든 도우미 열(helper column)을 대상으로 Region과 Band를 한 번에 일치시켜요. - 양방향 조회
- 이어 붙인 키 방식은
INDEX/MATCH에서도 똑같이 통해요.=INDEX($H$2:$H$7,MATCH(B2&"-"&C2,$F$2:$F$7,0))— 결과는 같고, 시트의 다른 곳에서INDEX의 유연성이 필요할 때 유용해요. XMATCH=XMATCH(value,range)수식은 값이 아니라 위치를 반환해요.INDEX함수에 필요한 기본 재료예요.INDEX+MATCH=INDEX(returnRange,MATCH(value,lookupRange,0))—XLOOKUP을 두 함수로 구현한 버전이에요. 오래된 통합 문서에 자주 나오니 지금도 알아 둘 만해요.- 구간을 위한 근사 일치(approximate match)
- 오름차순으로 정렬한 기준값 목록에
MATCH(value,range,1)을 쓰면, 값이 넘어서는 마지막 기준값을 찾아 줘요. 커미션 등급이나 가격 구간에 딱 필요한 기능이에요.
커미션 요율 표 — F2:H7에 있어요
| F — Key (Region-Band) | H — Rate |
|---|---|
| East-A | 4% |
| East-B | 6% |
| West-A | 5% |
| West-B | 7% |
| Central-A | 3% |
| Central-B | 5% |
각 키는 Region과 Band를 대시(-)로 이어 붙인 값이에요. 조회하는 쪽과 조회 대상 쪽 모두 같은 방식으로 만들어요.
판매 등급 구간 — J2:K4에 있어요
| J — Minimum Sales | K — Tier |
|---|---|
| 0 | Bronze |
| 15,000 | Silver |
| 25,000 | Gold |
엑셀에서 찾는 위치
수식›찾기/참조 영역›INDEX · XMATCH
자동 저장 Book1.xlsx · 저장됨 검색–□✕
파일홈삽입페이지 레이아웃수식데이터검토보기자동화도움말
함수 삽입
자동 합계
최근 사용 항목
재무
논리
텍스트
날짜 및 시간
찾기/참조 영역
수학/삼각
함수 더 보기
함수 라이브러리
이름 관리자
이름 정의
수식에서 사용
선택 영역에서 만들기
정의된 이름
참조되는 셀 추적
참조하는 셀 추적
연결선 제거
수식 표시
오류 검사
수식 계산
조사식 창
수식 분석
계산 옵션
지금 계산
시트 계산
계산
| A | B | C | D | E | F | G | H | ||
|---|---|---|---|---|---|---|---|---|---|
| 1 | 지역 | Q1 | Q2 | Q3 | Q4 | 조회 | |||
| 2 | 북부 | 120 | 135 | 150 | 160 | 지역 | 남부 | ||
| 3 | 남부 | 98 | 104 | 117 | 121 | 분기 | Q3 | ||
| 4 | 동부 | 110 | 108 | 125 | 131 | 결과 | 117 | ||
| 5 | 서부 | 87 | 95 | 99 | 112 | ||||
| 6 | |||||||||
| 7 | |||||||||
| 8 |
◀ ▶Sheet1+
준비접근성: 계속 진행 가능▦ ▤ ▥ — + 100%
옆으로 밀어 창 전체를 볼 수 있어요.
- 1INDEX, MATCH, XMATCH, XLOOKUP은 찾기/참조 영역에 있어요.
- 2XMATCH 하나는 행을, 다른 하나는 열을 찾고 INDEX가 교차하는 값을 돌려줘요.
- 3남부 × Q3의 값.
- 4이 입력값을 바꾸면 결과가 바로 바뀌어요.
Microsoft 365용 Excel(Windows) 화면을 재현한 그림입니다. Mac, 웹용 Excel 또는 좁은 창에서는 리본 배치가 조금 다를 수 있습니다.
따라 만들기
위에 Region과 Band를 키로 하는 커미션 요율 표가 있어요. 각 담당자의 요율을 두 가지 방법으로 조회한 다음, 근사 일치로 담당자를 판매 등급에 배정하세요.
혼자 응용하기
나만의 요율 표 만들기
실제로 쓰는 2차원 요율표나 가격표(무게와 지역별 배송비, 주와 품목별 세율 등)를 하나 골라, 이어 붙인 키를 쓰는 XLOOKUP 버전과 같은 조회를 하는 INDEX/MATCH 버전을 모두 만들어 보세요.
이 레슨에서 틀린 부분을 찾았거나 제안이 있나요? 의견 보내기