엑셀 LEFT로 상품코드 분류했는데 VLOOKUP #N/A 뜰 때 초보자용 확인법

엑셀에서 상품코드 앞글자로 분류를 자동 입력하려고 LEFT 함수와 VLOOKUP을 썼는데, 결과가 #N/A로 뜨는 경우가 정말 많습니다. 특히 쇼핑몰 주문내역, 재고 리스트, 매출 자료처럼 상품코드가 길게 들어있는 파일에서 초보자가 자주 검색하는 문제가 바로 “엑셀 VLOOKUP 안됨”, “LEFT 함수로 뽑았는데 안 찾아짐”입니다.

오늘은 어려운 기능 없이, 실제 파일에서 바로 만나는 구조로 차근차근 보겠습니다. 핵심은 상품코드 앞 2글자만 잘라서 대분류표에서 찾아오는 것입니다. 그런데 코드 앞뒤 공백, 숫자처럼 보이는 코드, 참조 범위 위치 때문에 생각보다 쉽게 틀어집니다.

실무에서 자주 터지는 상황: 상품코드 앞 2글자로 대분류 넣기

예를 들어 주문내역 시트에 아래처럼 데이터가 있다고 해보겠습니다. 실제 업무에서는 행이 수천 개일 수 있지만, 원리는 3행부터 8행까지 있는 작은 표로 먼저 익히면 됩니다.

A열은 주문번호, B열은 상품코드, C열은 상품명, D열은 수량, E열은 단가, F열은 대분류 결과입니다. 결과를 표시할 셀은 F3부터 F8까지입니다.

그리고 같은 시트의 오른쪽에 분류 기준표를 둡니다. H2:I5 범위에 만들고, H열에는 상품코드 앞 2글자, I열에는 대분류명을 입력합니다.

여기서 하고 싶은 일은 간단합니다. B3의 EL-1001에서 앞 2글자인 EL만 뽑고, H3:I5 표에서 EL을 찾아 전자제품을 F3에 표시하는 것입니다.

처음 넣기 쉬운 수식과 왜 헷갈리는지

F3 셀에 아래 수식을 입력해 보겠습니다. 수식을 입력하는 위치는 반드시 결과를 표시할 첫 셀인 F3입니다. 입력 후 Enter를 누르고, F3 셀의 오른쪽 아래 작은 점을 F8까지 끌어내리면 됩니다.

=VLOOKUP(LEFT(B3,2),$H$3:$I$5,2,FALSE)

여기서 LEFT(B3,2)는 B3 셀의 왼쪽에서 2글자를 가져오라는 뜻입니다. EL-1001이라면 EL만 가져옵니다. VLOOKUP은 그 EL을 H3:I5 범위의 첫 번째 열인 H열에서 찾고, 같은 행의 두 번째 열인 I열 값을 가져옵니다.

초보자가 가장 많이 헷갈리는 부분은 $H$3:$I$5처럼 달러 표시가 붙은 범위입니다. 이것은 수식을 아래로 복사해도 분류표 범위가 움직이지 않게 고정한다는 뜻입니다. 만약 H3:I5처럼 그냥 쓰면 F4로 내려갔을 때 H4:I6으로 밀려서, 첫 번째 분류코드인 EL을 못 찾는 일이 생깁니다.

흔한 실수: 수식은 맞는데 #N/A가 뜨는 이유

수식 모양은 맞는데 #N/A가 뜬다면, 대부분은 “찾는 값”과 “분류표의 값”이 눈으로 보기에는 같지만 엑셀 입장에서는 다른 값인 경우입니다. 실무에서는 아래 경우가 정말 많습니다.

• 상품코드 앞에 공백이 숨어 있음: B3 셀이 실제로는 공백+EL-1001일 수 있습니다.
• 분류표 코드에 공백이 있음: H3 셀이 EL처럼 보이지만 뒤에 공백이 붙어 있을 수 있습니다.
• 분류표 범위를 고정하지 않음: 수식을 아래로 복사하면서 H3:I5가 밀립니다.
• 앞 2글자가 아닌 앞 3글자를 잘라야 하는 코드: 예를 들어 EL1-1001처럼 코드 체계가 다르면 LEFT(B3,2)가 맞지 않습니다.

특히 공백은 눈에 잘 안 보이기 때문에 초보자에게 가장 얄미운 문제입니다. 거래처나 쇼핑몰에서 받은 주문 파일을 복사해 붙여넣으면 상품코드 앞뒤에 빈칸이 들어오는 경우가 있습니다. 이때는 LEFT가 그 공백까지 글자로 인식해서, 앞 2글자가 EL이 아니라 “공백+E”가 됩니다.

안전한 처리법: 공백을 제거하고 찾기

공백 때문에 틀어지는 파일이라면 F3에 아래 수식을 넣는 편이 더 안전합니다. 여기서 TRIM은 셀 앞뒤의 불필요한 공백을 정리해 주는 함수입니다.

=VLOOKUP(LEFT(TRIM(B3),2),$H$3:$I$5,2,FALSE)

이 수식은 먼저 B3의 공백을 정리하고, 그다음 왼쪽 2글자를 가져옵니다. 즉 “ EL-1001”처럼 앞에 빈칸이 들어와도 EL을 안정적으로 뽑아낼 수 있습니다.

결과가 없는 코드도 깔끔하게 표시하고 싶다면 IFERROR를 같이 쓰면 좋습니다. 예를 들어 분류표에 없는 코드가 들어왔을 때 #N/A 대신 “분류확인”이라고 표시할 수 있습니다.

=IFERROR(VLOOKUP(LEFT(TRIM(B3),2),$H$3:$I$5,2,FALSE),"분류확인")

이 수식은 실무에서 꽤 편합니다. #N/A 오류가 화면에 쭉 뜨면 어디부터 봐야 할지 부담스러운데, “분류확인”이라고 나오면 기준표에 없는 새 상품코드인지 확인하면 됩니다.

XLOOKUP을 쓸 수 있다면 이렇게도 가능

Microsoft 365나 Excel 2021 이상을 사용한다면 XLOOKUP 함수도 많이 씁니다. 같은 상황에서 F3에는 아래처럼 입력할 수 있습니다.

=XLOOKUP(LEFT(TRIM(B3),2),$H$3:$H$5,$I$3:$I$5,"분류확인")

XLOOKUP은 찾을 범위와 가져올 범위를 따로 지정합니다. $H$3:$H$5에서 코드를 찾고, $I$3:$I$5에서 대분류명을 가져옵니다. 마지막의 “분류확인”은 찾지 못했을 때 보여줄 문구입니다.

VLOOKUP이 익숙한 파일이라면 VLOOKUP을 계속 써도 괜찮습니다. 다만 새로 배우는 분이라면 XLOOKUP이 범위 구조를 이해하기 조금 더 쉽습니다. 어느 쪽이든 중요한 것은 LEFT로 잘라낸 코드와 기준표 코드가 정확히 같은지입니다.

점검 포인트: 내 파일에서 어디를 바꿔야 할까

예제와 실제 파일의 열 위치가 다르다면 수식에서 바꿔야 할 부분만 정확히 보면 됩니다. 상품코드가 B열이 아니라 C열에 있다면 B3을 C3으로 바꾸면 됩니다. 분류표가 H:I열이 아니라 K:L열에 있다면 $H$3:$I$5를 실제 분류표 범위로 바꾸면 됩니다.

VLOOKUP에서 특히 중요한 규칙이 하나 있습니다. 찾을 값은 반드시 지정한 표 범위의 첫 번째 열에 있어야 합니다. 위 예제에서는 EL, FD, CL이 H열에 있으므로 H3:I5 범위를 잡는 것이 맞습니다. 만약 I열에 코드가 있고 H열에 대분류명이 있다면 VLOOKUP으로는 그대로 찾기 어렵기 때문에 표 위치를 바꾸거나 XLOOKUP을 쓰는 편이 낫습니다.

자동화 팁: 새 주문이 추가될 때마다 수식 복사하기

매일 주문내역을 붙여넣는 파일이라면 F열 수식을 한 번만 만들고 끝내기 어렵습니다. 새 데이터가 9행, 10행으로 계속 추가되면 F열도 같이 내려줘야 합니다.

가장 쉬운 방법은 F3에 완성 수식을 넣은 뒤, F3 셀의 오른쪽 아래 모서리에 마우스를 올리는 것입니다. 작은 검은 십자가 모양이 보이면 더블클릭해 보세요. 왼쪽 B열이나 C열에 데이터가 이어져 있으면, 엑셀이 마지막 행 근처까지 수식을 자동으로 채워줍니다.

또 하나의 습관은 기준표 범위를 넉넉하게 잡는 것입니다. 분류코드가 앞으로 늘어날 수 있다면 H3:I5 대신 H3:I100 정도로 잡아도 됩니다. 예를 들어 VLOOKUP 수식은 아래처럼 바꿀 수 있습니다.

=IFERROR(VLOOKUP(LEFT(TRIM(B3),2),$H$3:$I$100,2,FALSE),"분류확인")

이렇게 해두면 새 분류코드를 H6, H7에 추가해도 수식을 매번 고치지 않아도 됩니다. 단, H열 첫 번째 열에는 반드시 분류코드가 들어가야 하고, I열에는 대분류명이 들어가야 합니다.

실무 체크: 오류가 나면 수식보다 원본부터 보기

상품코드 분류 자동 입력이 안 될 때는 수식을 계속 고치기 전에 세 가지만 먼저 확인하면 시간이 줄어듭니다. 상품코드 앞에 공백이 있는지, 분류표 범위가 $로 고정되어 있는지, LEFT로 자르는 글자 수가 실제 코드 체계와 맞는지입니다.

처음에는 F3 한 셀에서만 결과가 맞는지 확인하고, 그다음 F8까지 복사하세요. 한 셀에서 틀린 수식을 수천 행에 복사하면 나중에 원인을 찾기가 더 어려워집니다. 작은 범위에서 맞춘 뒤 전체 데이터로 확장하는 습관이 가장 안전합니다.