[엑셀 실무편] XLOOKUP + MID/RIGHT
코드형 데이터 해석과 자동 분류
엑셀 실무를 하다 보면
계산은 맞는데, 데이터가 일을 안 하는 경우를 자주 겪습니다.
코드가 있는데 무슨 의미인지 알 수 없고
앞자리·뒷자리에 정보가 숨어 있는데 활용이 안 되고
분류 기준이 코드 안에 들어 있지만 꺼내 쓰지 못하는 상태
숫자도 맞고 조회도 되는데,
이 상태로는 바로 보고서를 만들 수 없습니다.
이번 화에서는 아래 내요을 다룹니다.
XLOOKUP으로 코드에 ‘기본 의미’를 붙이고
LEFT / MID / RIGHT로 코드 안에 숨은 구조를 해석하는 방법
이 조합을 쓰기 시작하면
엑셀 데이터는 단순 나열이 아니라
분석 가능한 구조로 바뀝니다.
실무에서 코드형 데이터가 문제가 되는 이유
실무 데이터의 상당수는
사람이 만든 의미 있는 코드입니다.
예를 들면,
P-EL-001
A202403
HR-N-05
이 코드들은
계산에는 문제가 없지만,
그대로는 집계·분류·분석이 어렵습니다.
그래서 실무에서는 이렇게 역할을 나눕니다.
XLOOKUP → 코드에 의미를 붙이는 역할
LEFT / MID / RIGHT → 코드 구조를 해석하는 역할
이 두 단계를 분리하는 순간,
엑셀 구조가 훨씬 명확해집니다.
실무 시나리오 예시
다음 상황을 가정해 보겠습니다.
✔ 주문 데이터 Sheet (Order ID / Product Code / Amount)
✔ 상품 코드 Sheet (Product Code / Product Name)
✔ 보고서 Sheet (카테고리별 매출 요약)
문제는 이겁니다.
Product Code 하나에 정보가 너무 많이 들어 있음
그대로는 카테고리별 집계가 불가능
이건 한 번에 풀 문제가 아니라, 단계적으로 풀어야 합니다.
Step 1. XLOOKUP + MID / RIGHT로 ‘기본 의미’부터 연결하기
먼저,
Product Code 기준으로 기본 정보를 붙입니다.
✔ Category Code 연결
=MID(XLOOKUP(A2,'Product Code'!B:B,'Product Code'!A:A),3, 2)
✔ Type 연결
=RIGHT(XLOOKUP(A2,'Product Code'!B:B,'Product Code'!A:A),3)
이 단계가 끝나면
데이터는 더 이상 단순 코드가 아닙니다.
코드 하나로 분류 / 집계 / 필터링이 가능한 상태가 됩니다.
Step 2. XLOOKUP 결과 + 코드 분해 값 조합하기
실무에서는 보통 이렇게 씁니다.
XLOOKUP → 기준 정보 연결
코드 분해 Column → 집계 기준으로 사용
✔ 제품별 매출 합계
=SUMIFS(Order!$C:$C,Order!$B:$B,XLOOKUP(Report!A2,'Product Code'!B:B,'Product Code'!A:A))
✔ Category Code 기준 매출 합계
=SUMIFS($D$2:$D$6, $B$2:$B$6,A9)
이때부터 엑셀은
“조회하는 파일”이 아니라
분석 구조를 가진 데이터”가 됩니다.
이 조합이 실무에서 강력한 이유
XLOOKUP + LEFT / MID / RIGHT 조합의 핵심은 이겁니다.
코드 구조가 바뀌어도 대응 가능
분류 기준을 수식으로 관리
집계·보고서 수식이 단순해짐
유지보수 포인트가 명확함
즉,
❌ 코드 의미를 사람이 기억하는 구조
⭕ 코드 의미를 엑셀이 해석하는 구조
이 차이가 실무 효율을 만듭니다.
실무에서 자주 쓰는 패턴 정리
이 조합은 이런 상황에서 특히 유용합니다.
상품 코드 기반 카테고리 분류
부서 코드 앞자리 기준 집계
관리 번호 체계 자동 분해
ERP / 매출 / 인사 데이터 다룬다면
거의 필수 패턴입니다.
실무자가 체감하는 Before / After
❌ Before
코드 기준 수동 분류
보고서마다 기준 다시 설정
코드 변경 시 전체 수정
✔ After
코드 구조 자동 해석
기준 Column 고정
보고서 형식만 바꿔도 데이터는 유지
“엑셀을 많이 아는 사람”보다
"데이터 구조를 설계한 사람”처럼 보이게 됩니다.
오늘의 핵심 정리
✔ XLOOKUP은 코드에 의미를 붙인다
✔ LEFT / MID / RIGHT는 코드 구조를 해석한다
✔ 코드형 데이터는 반드시 분해해서 써야 한다
✔ 실무 엑셀은 계산보다 구조가 먼저다