왜 조회 결과가 자주 깨질까 — 가격표나 코드표는 살아 있는 문서입니다. 신규 코드가 추가되고 구 모델은 단종됩니다. 이런 변화가 있을 때 VLOOKUP이나 XLOOKUP이 그대로라면 결과 셀에 오류가 퍼지고 그 값에 의존한 합계와 리포트까지 연쇄적으로 깨집니다. 현장 업무에서는 오류를 감추는 것이 아니라 예상하고 흡수하는 구조가 중요합니다. 이 글은 Excel 365와 Google Sheets에서 IFERROR와 ISNA를 이용해 누락을 안전하게 처리하고, 신규 코드를 탐지하기 위한 플래그와 임시 금액 정책까지 포함한 깨지지 않는 조회체계를 단계별로 설계합니다. 샘플 CSV와 결과 CSV를 함께 제공하니 그대로 내려받아 재현해 보실 수 있습니다.
파일 내려받기
- 상품 마스터 CSV: B8_products_sample.csv
- 주문 목록 CSV: B8_orders_sample.csv
- 조회 결과 CSV: B8_lookup_result.csv
샘플 데이터 설명
샘플은 두 파일로 구성됩니다. Products에는 SKU와 이름, 단가가 있고 Orders에는 주문번호, SKU, 수량이 있습니다. 일부 주문은 마스터에 없는 새로운 SKU를 의도적으로 포함합니다. 결과 파일은 누락을 감지해 안전한 기본값을 적용하고, 신규 코드 검토 메모를 자동으로 남깁니다.
상품 마스터 B8_products_sample.csv
| 컬럼 | 의미 | 예시 | 비고 |
|---|---|---|---|
sku |
상품 코드 | P018 | 고유 키 |
name |
상품명 | Marker | 문자 |
price |
단가 | 4200 | 정수 |
주문 목록 B8_orders_sample.csv
| 컬럼 | 의미 | 예시 | 비고 |
|---|---|---|---|
order_id |
주문 번호 | O-0012 | 문자 |
sku |
주문 코드 | P032 | 의도적으로 마스터에 없을 수 있음 |
qty |
수량 | 6 | 정수 |
결과 B8_lookup_result.csv
| 컬럼 | 의미 | 예시 | 처리 규칙 |
|---|---|---|---|
name |
상품명 | 미등록 | 없으면 미등록로 대체 |
price |
단가 | 0 | 없으면 0으로 대체 |
amount |
금액 | 0 | qty×price |
not_found |
누락 여부 | TRUE | ISNA 또는 오류 감지 |
note |
메모 | 신규코드검토 | 누락이면 안내 문구 |
설계 원칙. 오류를 예외가 아닌 상태로 다루기
핵심은 두 가지입니다. 첫째, 조회 실패를 공백이나 오류로 남기지 않고 정의된 기본값으로 치환합니다. 예를 들어 이름은 미등록, 가격은 0. 둘째, 실패 자체는 숨기지 않고 플래그로 기록합니다. 이후 품목관리나 재고 마스터 업데이트의 작업 대상을 쉽게 찾기 위해서입니다. 이 두 값을 함께 저장하면 합계는 안전하게 계산되면서도 누락 건은 놓치지 않습니다.
Excel 365 레시피
이름과 가격 조회를 안전하게
=IFERROR(XLOOKUP([@sku], Products[sku], Products[name]), "미등록")
=IFERROR(XLOOKUP([@sku], Products[sku], Products[price]), 0)오류가 나면 즉시 기본값으로 치환합니다. 구조적 참조를 쓰면 열 이동에도 깨지지 않습니다.
누락 플래그와 금액 계산
=ISNA(XMATCH([@sku], Products[sku], 0))
=[@qty] * IFERROR(XLOOKUP([@sku], Products[sku], Products[price]), 0)XMATCH는 찾지 못하면 NA를 내므로 ISNA로 플래그를 얻습니다. 금액은 방어적 가격을 곱해 항상 계산됩니다.
문장형 알림과 정책 분리
=IF([@not_found], "신규코드검토", "")정책 값을 한 셀로 분리하면 문구 변경이 필요할 때 수식을 건드리지 않아도 됩니다.
Google Sheets 레시피
VLOOKUP과 IFERROR 조합
=IFERROR(VLOOKUP(B2, Products!A:C, 2, FALSE), "미등록")
=IFERROR(VLOOKUP(B2, Products!A:C, 3, FALSE), 0)오류를 기본값으로 바꿉니다. VLOOKUP은 열 순서에 민감하므로 가능한 INDEX MATCH로 옮기는 것을 권장하지만, 간단한 예시에서는 이해가 쉽습니다.
MATCH로 누락 감지
=ISNA(MATCH(B2, Products!A:A, 0))정확 일치로 없으면 NA가 되며, 이를 ISNA로 TRUE FALSE로 바꿉니다.
파손을 막는 보조 규칙
- 문자열 공백과 대소문자를 먼저 정규화합니다. 입력 단계에서 TRIM과 UPPER를 적용하면 조회 실패가 급감합니다.
- 가격 기본값 0은 보고용 정책입니다. 매출 누락을 막기 위한 경고 알림을 별도로 두는 것이 좋습니다.
- 결과 열은 원본 옆이 아니라 별도의 요약 시트에 두면 운영 중 구조가 흔들리지 않습니다.
검증 스니펫
-- 누락 플래그와 이름 기본값의 일관성
=COUNTIFS(결과[name], "미등록", 결과[not_found], TRUE) = COUNTIF(결과[not_found], TRUE)
-- 가격 0인 행 중 플래그가 FALSE인 비정상 케이스 탐지
=COUNTIFS(결과[price], 0, 결과[not_found], FALSE) = 0
-- 총액 보전 테스트
=SUM(결과[amount]) = SUM(결과[qty]*IFERROR(조회가격,0))샘플을 시트에 바로 적용하는 빠른 경로
- Products CSV를 불러와 시트 이름을 Products로 저장합니다.
- Orders CSV를 불러와 시트 이름을 Orders로 저장합니다. A열 주문번호, B열 SKU, C열 수량입니다.
- Orders 시트 D열부터 다음 수식을 넣고 아래로 채웁니다. D2
=IFERROR(XLOOKUP(B2,Products!A:A,Products!B:B),"미등록")E2=IFERROR(XLOOKUP(B2,Products!A:A,Products!C:C),0)F2=C2*E2G2=ISNA(XMATCH(B2,Products!A:A,0))H2=IF(G2,"신규코드검토","")
정리
조회는 실패할 수 있습니다. 중요한 것은 실패를 예외가 아니라 상태로 다루는 체계입니다. IFERROR로 기본값을 정의하고 ISNA로 누락을 명확히 표시하면, 합계와 리포트는 안정적으로 돌아가면서도 마스터 유지보수 업무는 더 쉬워집니다. 위 CSV를 내려받아 여러분의 시트에 곧바로 적용해 보세요. 파손 없이 운영하는 조회체계가 표준이 되면, 데이터 품질과 보고 속도가 함께 올라갑니다.