엑셀 주문번호 앞자리로 채널명 자동 입력하기: LEFT와 VLOOKUP 초보 실무 예제

엑셀에서 주문번호 앞자리로 채널명 자동 입력하는 방법을 찾는 경우가 꽤 많습니다. 주문번호는 있는데 이게 스마트스토어 주문인지, 쿠팡 주문인지, 자사몰 주문인지 매번 눈으로 보고 적다 보면 꼭 오타가 납니다.

오늘은 아주 작은 주문표 하나를 끝까지 다듬어 보겠습니다. 핵심은 LEFT 함수로 주문번호 앞 3글자를 잘라내고, VLOOKUP 함수로 코드표에서 채널명을 찾아오는 것입니다. 초보자분들이 헷갈리기 쉬운 셀 위치, 범위 고정, 왜 #N/A가 뜨는지도 같이 짚어볼게요.

실무에서 자주 만나는 주문표 상황

아래와 같은 주문 원장이 있다고 가정하겠습니다. 데이터는 5행부터 시작하고, 4행은 제목 행입니다. A열부터 H열까지 주문 내역을 정리할 예정입니다.

예시 데이터는 이렇게 입력해 보겠습니다. 실제 파일에서는 이미 A~E열까지만 있고, F~H열을 새로 만들어야 하는 상황이 많습니다.

먼저 매출액부터 계산해 두기

채널별 합계를 보려면 각 주문의 매출액이 먼저 있어야 합니다. F4 셀에는 매출액이라고 입력하고, F5 셀에 아래 수식을 넣습니다.

=D5*E5

D5는 수량, E5는 단가입니다. 즉 2개를 12,000원에 팔았으니 F5에는 24,000이 나옵니다. F5 셀 오른쪽 아래 작은 네모를 잡고 F10까지 아래로 끌어 복사하면 나머지 행도 계산됩니다.

여기서 초보자분들이 자주 놓치는 부분이 있습니다. 셀에 수식을 입력할 때는 반드시 =로 시작해야 합니다. 그냥 D5*E5라고 쓰면 엑셀은 계산식이 아니라 글자로 볼 수 있습니다.

주문번호 앞 3글자를 G열에 꺼내기

이제 주문번호에서 앞 3글자만 꺼내 보겠습니다. B열 주문번호를 보면 STO, COU, WEB처럼 맨 앞 3글자가 판매 채널을 뜻합니다.

G4 셀에는 채널코드라고 입력하고, G5 셀에 아래 수식을 입력합니다.

=LEFT(B5,3)

LEFT 함수는 이름 그대로 왼쪽에서 글자를 가져오는 함수입니다. 괄호 안의 B5는 가져올 원본 셀이고, 3은 왼쪽에서 3글자를 가져오라는 뜻입니다.

예를 들어 B5에 STO-260601-001이 들어 있다면, LEFT(B5,3)의 결과는 STO가 됩니다. 이 수식을 G10까지 복사하면 각 주문번호 앞자리 코드가 자동으로 뽑힙니다.

코드표를 오른쪽에 작게 만들기

이제 STO가 스마트스토어인지, COU가 쿠팡인지 알려주는 코드표가 필요합니다. 같은 시트의 J4:K8 영역에 작은 표를 만들겠습니다.

이 코드표에서 중요한 점은 찾을 값인 채널코드가 왼쪽 열에 있어야 한다는 것입니다. VLOOKUP은 기본적으로 표의 첫 번째 열에서 값을 찾고, 그 오른쪽 열의 값을 가져오는 방식이기 때문입니다.

VLOOKUP으로 채널명 자동 입력하기

H4 셀에는 채널명이라고 입력합니다. 그리고 H5 셀에 아래 수식을 입력해 주세요.

=VLOOKUP(G5,$J$5:$K$8,2,FALSE)

수식이 조금 길어 보이지만, 뜻을 나누면 어렵지 않습니다. G5에 있는 채널코드를 J5:K8 코드표에서 찾고, 그 표의 2번째 열인 채널명을 가져오라는 뜻입니다.

여기서 $J$5:$K$8처럼 달러 표시가 붙은 이유도 꼭 알아두면 좋습니다. H5 수식을 H10까지 아래로 복사할 때, 코드표 위치가 J6:K9, J7:K10처럼 같이 밀리면 안 됩니다. 그래서 코드표 범위를 고정해 두는 것입니다.

달러 표시는 직접 입력해도 되고, 수식 입력 중 J5:K8 범위를 선택한 뒤 키보드의 F4를 눌러 넣을 수도 있습니다.

#N/A가 뜰 때는 수식보다 값을 먼저 확인하기

VLOOKUP을 쓰다 보면 H열에 #N/A 오류가 뜨는 경우가 있습니다. 이 오류는 대부분 “찾는 값이 코드표에 없다”는 뜻입니다.

예를 들어 주문번호가 NVR-260604-001인데 코드표 J5:J8에 NVR이 없다면 VLOOKUP은 채널명을 가져올 수 없습니다. 이때는 수식이 틀린 게 아니라 코드표에 새 코드를 추가해야 합니다.

또 하나 흔한 실수는 주문번호 앞에 빈칸이 들어간 경우입니다. 겉으로는 STO-260601-001처럼 보여도 실제로는 앞에 공백이 있어서 LEFT(B5,3)의 결과가 STO가 아니라 빈칸 포함 3글자가 될 수 있습니다. 이럴 때는 원본 주문번호를 클릭해 수식 입력줄에서 앞뒤에 이상한 공백이 없는지 먼저 확인해 보세요.

오류가 화면에 보이는 것이 부담스럽다면 H5 수식을 아래처럼 바꿀 수 있습니다.

=IFERROR(VLOOKUP(G5,$J$5:$K$8,2,FALSE),"확인필요")

IFERROR는 앞의 수식이 오류가 아닐 때는 원래 결과를 보여주고, 오류일 때는 뒤에 적은 값을 보여줍니다. 여기서는 코드표에 없는 코드가 나오면 확인필요라고 표시되게 한 것입니다.

채널별 주문 건수와 매출 합계까지 보기

이제 H열에 채널명이 자동으로 들어왔으니, 작은 집계표도 만들 수 있습니다. M4:N7 영역에 채널별 주문 건수와 매출 합계를 만들어 보겠습니다.

M5에는 스마트스토어, M6에는 쿠팡, M7에는 자사몰을 입력합니다. N5 셀에는 스마트스토어 주문이 몇 건인지 세는 COUNTIF 수식을 넣습니다.

=COUNTIF($H$5:$H$10,M5)

COUNTIF는 조건에 맞는 셀 개수를 세는 함수입니다. H5:H10에서 M5에 적힌 스마트스토어와 같은 값이 몇 개인지 세어 줍니다. 이 수식을 N7까지 아래로 복사하면 쿠팡, 자사몰 건수도 계산됩니다.

이번에는 O5 셀에 매출 합계를 구하겠습니다.

=SUMIF($H$5:$H$10,M5,$F$5:$F$10)

SUMIF는 조건에 맞는 행의 금액만 더하는 함수입니다. H5:H10에서 채널명이 M5와 같은 행을 찾고, 그 행의 F5:F10 매출액을 더합니다.

여기서도 범위를 고정한 이유는 같습니다. O5 수식을 아래로 복사할 때 H5:H10과 F5:F10 범위는 움직이면 안 되고, 조건 셀 M5만 M6, M7로 바뀌어야 하기 때문입니다.

초보자가 특히 많이 헷갈리는 포인트

첫째, LEFT 함수 결과는 숫자 계산이 아니라 글자 추출입니다. 주문번호처럼 문자와 숫자, 하이픈이 섞인 값에서 필요한 부분을 잘라낼 때 사용한다고 생각하면 쉽습니다.

둘째, VLOOKUP의 코드표는 찾을 값이 왼쪽에 있어야 합니다. 채널명이 J열이고 코드가 K열이면 VLOOKUP으로는 바로 찾기 어렵습니다. 초보 단계에서는 코드표를 코드 왼쪽, 이름 오른쪽 구조로 만드는 습관을 들이는 편이 안전합니다.

셋째, FALSE를 빼먹지 않는 것이 좋습니다. 마지막 인수에 FALSE를 넣으면 정확히 같은 값만 찾습니다. 코드처럼 정확해야 하는 값은 거의 항상 FALSE를 쓴다고 기억하면 됩니다.

넷째, 복사할 수식에는 고정할 범위와 움직일 셀을 구분해야 합니다. 코드표 범위, 집계 범위처럼 계속 같은 곳을 봐야 하는 범위는 $ 표시로 고정하고, G5나 M5처럼 행마다 바뀌어야 하는 셀은 그대로 둡니다.

내 파일에 적용할 때 확인할 순서

실제 파일에 적용할 때는 먼저 주문번호의 규칙을 확인하세요. 오늘 예제는 앞 3글자가 채널코드였지만, 어떤 회사 파일은 앞 2글자이거나 하이픈 앞까지가 코드일 수 있습니다.

앞 2글자라면 G5 수식은 이렇게 바뀝니다.

=LEFT(B5,2)

반대로 오른쪽 끝 3글자가 지점 코드라면 LEFT가 아니라 RIGHT를 씁니다.

=RIGHT(B5,3)

중간에 있는 글자를 꺼내야 할 때는 MID 함수를 사용합니다. 예를 들어 주문번호의 5번째 글자부터 6글자를 가져오고 싶다면 아래처럼 씁니다.

=MID(B5,5,6)

함수 이름은 달라도 생각하는 방식은 비슷합니다. 어느 셀에서 가져올지, 몇 번째 글자부터 볼지, 몇 글자를 가져올지만 정하면 됩니다.

이 예제를 조금 더 키워보면

오늘 만든 표는 단순하지만 실제 업무에서 꽤 많이 쓰입니다. 주문번호 앞자리로 채널명을 붙이고, 채널별 주문 건수와 매출 합계까지 한 번에 볼 수 있기 때문입니다.

다음에는 같은 방식으로 상품코드 앞자리를 잘라 카테고리를 붙이거나, 거래처코드로 담당자를 자동 입력할 수도 있습니다. 핵심은 원본 표 옆에 보조열을 만들고, 코드표를 기준으로 정확히 찾아오게 만드는 것입니다.