왜 XMATCH와 INDEX인가 — 범주와 지역과 규격처럼 조건이 셋 이상인 조회는 단순 검색으로는 불안정해지기 쉽습니다. 피벗으로 우회해도 보고 흐름이 끊기고, 일회용 VLOOKUP은 열이 추가되면 깨집니다. XMATCH와 INDEX 조합은 배열을 직접 다루면서도 계산 흐름이 단순하고 빠릅니다. 조건을 논리 벡터로 만들고 XMATCH로 한 번에 일치 행 번호를 구한 뒤, INDEX로 원하는 열을 정확하게 꺼내면 됩니다. 이 글은 정확 매칭, 기본값 폴백, 근사 규칙을 차례로 설계하는 과정을 담았습니다. 바로 재현하실 수 있도록 카탈로그와 요청 목록, 그리고 결과 CSV를 함께 제공합니다.
샘플 내려받기
- 카탈로그 CSV: B2_xmatch_catalog.csv
- 요청 목록 CSV: B2_xmatch_requests.csv
- 조회 결과 CSV: B2_xmatch_result.csv
샘플 데이터 설명
카탈로그 B2_xmatch_catalog.csv
| 컬럼 | 의미 | 예시 | 비고 |
|---|---|---|---|
region |
적용 지역 | Seoul | All은 지역 폴백용 기본값 |
category |
품목군 | Stationery | 문자열 |
size |
규격 | M | S M L XL |
color |
참고 속성 | Blue | 조회에는 직접 사용하지 않음 |
sku |
SKU | SKU-0007 | 반환 대상 |
price |
가격 | 4800 | 반환 대상 |
stock |
재고 | 35 | 반환 대상 |
요청 목록 B2_xmatch_requests.csv
| 컬럼 | 의미 | 예시 | 비고 |
|---|---|---|---|
req_id |
요청 번호 | REQ-003 | 표시용 |
region |
요청 지역 | Busan | 카탈로그와 동일 체계 |
category |
품목군 | Accessory | 정확 매칭 |
size |
규격 | L | 정확 또는 근사 매칭 |
preferred_color |
선호 색상 | Gray | 설명용 필드 |
결과 B2_xmatch_result.csv
| 컬럼 | 의미 | 예시 | 계산 개념 |
|---|---|---|---|
req_id |
요청 번호 | REQ-003 | 원본 유지 |
region |
적용된 지역 | Busan | 원본 유지 |
category |
품목군 | Accessory | 원본 유지 |
size |
규격 | L | 원본 유지 |
sku |
매칭된 SKU | SKU-0018 | 다중 조건 XMATCH 인덱스 기반 |
price |
가격 | 5400 | INDEX로 반환 |
stock |
재고 | 22 | INDEX로 반환 |
핵심 설계. 행 번호만 정확히 잡으면 끝
XMATCH의 포인트는 논리식의 곱입니다. 조건을 만족하면 TRUE, 아니면 FALSE가 나오는데, 이를 곱하면 참행에서만 1이 됩니다. 그 1의 위치가 곧 매칭 행입니다. 행 번호를 구했으면 INDEX로 어떤 열이든 안전하게 꺼낼 수 있습니다.
Excel 365 수식 레시피
정확 매칭 기본형
=LET(
cat, Catalog, -- 카탈로그 표 이름
r, [@region], c, [@category], s, [@size],
idx, XMATCH(1, (cat[region]=r) * (cat[category]=c) * (cat[size]=s), 0),
INDEX(cat[sku], idx)
)match_mode 0은 정확 일치입니다. 같은 idx를 이용해 INDEX(cat[price], idx)와 INDEX(cat[stock], idx)도 얻습니다.
헤더가 흔들리는 환경을 위한 합성 키 방식
=LET(
cat, Catalog,
key_arr, cat[region] & "|" & cat[category] & "|" & cat[size],
key_req, [@region] & "|" & [@category] & "|" & [@size],
idx, XMATCH(key_req, key_arr, 0),
INDEX(cat[sku], idx)
)키를 한 번 정의해 두면 열 순서가 바뀌거나 중간 열이 추가되어도 안전합니다.
폴백 1단계. 지역별이 없으면 All 지역으로 대체
=LET(
cat, Catalog,
r, [@region], c, [@category], s, [@size],
try1, IFERROR(
INDEX(cat[sku], XMATCH(1,(cat[region]=r)*(cat[category]=c)*(cat[size]=s),0)),
""
),
IF(try1<>"", try1,
LET(
cat_all, FILTER(cat, cat[region]="All"),
INDEX(cat_all[sku], XMATCH(1,(cat_all[category]=c)*(cat_all[size]=s),0))
)
)
)폴백 2단계. 규격 근사 매칭
요청 규격의 순서가 S M L XL로 정의되어 있다고 가정합니다. 가장 가까운 규격을 선택합니다.
=LET(
cat, Catalog,
r, [@region], c, [@category], s, [@size],
cat_all, FILTER(cat, cat[region]="All"),
ranks, XMATCH(cat_all[size], {"S","M","L","XL"}, 0),
req_rank, XMATCH(s, {"S","M","L","XL"}, 0),
dist, ABS(ranks - req_rank),
pick, XMATCH(MIN(dist), dist, 0),
INDEX(cat_all[sku], pick)
)실무에서는 위의 정밀 매칭과 폴백을 IFERROR로 순차 결합해 한 셀에서 처리하시면 됩니다.
여러 요청을 한 번에 처리하기
MAP을 사용하면 요청 표 전체를 한 수식으로 처리할 수 있습니다.
=LET(
cat, Catalog,
MAP(Requests[region], Requests[category], Requests[size],
LAMBDA(r,c,s,
LET(
idx, XMATCH(1,(cat[region]=r)*(cat[category]=c)*(cat[size]=s),0),
IFERROR(INDEX(cat[sku], idx),
LET(
cat_all, FILTER(cat, cat[region]="All"),
INDEX(cat_all[sku], XMATCH(1,(cat_all[category]=c)*(cat_all[size]=s),0))
)
)
)
)
)
)Google Sheets에서는 이렇게 적용합니다
Sheets에는 테이블 구조 참조가 없으므로 범위로 접근합니다. 합성 키가 가장 단순합니다.
-- 카탈로그 시트 A:C가 region category size, E가 sku, F가 price라고 가정
-- 요청 시트의 G열에 키를 만들고 H열에 SKU를 구하는 예
=LET(
key, D2 & "|" & E2 & "|" & F2,
karr, Catalog!A:A & "|" & Catalog!B:B & "|" & Catalog!C:C,
idx, MATCH(key, karr, 0),
IFERROR(INDEX(Catalog!E:E, idx),
INDEX(FILTER(Catalog!E:E, (Catalog!A:A="All")*(Catalog!B:B=E2)*(Catalog!C:C=F2)), 1)
)
)여러 행을 동시에 처리하려면 위 공식을 MAP으로 감싸 행별로 평가하거나, 보조열에 키를 만들어 VLOOKUP과 FILTER를 조합하는 방법도 좋습니다.
성능과 신뢰성을 높이는 작은 규칙
- 요청과 카탈로그 모두 공백과 대소문자 표기를 먼저 정규화하십시오. TRIM과 UPPER를 입력 단계에서 적용하면 조회 실패가 줄어듭니다.
- 표를 사용해 구조적 참조로 고정하십시오. 범위가 늘어도 수식이 유지됩니다.
- 폴백 규칙은 문서화하고 한 셀에 하드코딩하지 마십시오. 지역 기본값이 바뀌면 참조 셀 하나만 수정되도록 만들면 유지보수가 쉽습니다.
검증 스니펫
-- 조회 성공률
=COUNTIF(결과[sku], "<>") / COUNTA(결과[sku])
-- 폴백 사용 건수
=COUNTIF(결과[region], "All") -- 폴백일 때 region을 All로 표기한 경우
-- 중복 키 탐지
=COUNTIF(카탈로그키열, 카탈로그키열) > 1 을 조건부 서식으로 표시자주 생기는 문제와 해결책
- 두 행 이상이 동시에 일치 — 카탈로그 키가 실제로 고유하지 않다는 뜻입니다. 색상처럼 조건을 더 넣거나, 최신일자 기준으로 한 행을 선택하는 규칙을 추가하십시오.
- 근사 매칭이 오작동 — XMATCH의 match_mode를 정확히 이해해야 합니다. 규격 순서를 배열로 고정하고, 그 배열 상의 순번으로 비교해야 의도대로 작동합니다.
- 속도가 느림 — FILTER를 반복 호출하면 비용이 큽니다. LET으로 중간 배열을 변수에 담아 재사용하십시오.
마무리
다중 조건 조회의 본질은 결국 행 번호 찾기입니다. XMATCH는 이 과정을 빠르고 투명하게 만들어 줍니다. 여기에 INDEX를 결합하면 어떤 열이든 안정적으로 반환할 수 있습니다. 제공된 카탈로그와 요청 CSV를 내려받아 위 수식을 그대로 적용해 보시기 바랍니다. 정확 매칭에서 폴백과 근사까지 한 번에 구성하면, 가격표와 옵션표가 자주 바뀌는 환경에서도 흔들림 없이 조회 체계를 운영하실 수 있습니다.