엑셀 VLOOKUP 복사하면 안됨? 단가표 범위 고정해서 주문금액 자동 계산하기

엑셀에서 VLOOKUP 안됨, 단가가 이상하게 나옴, 아래로 복사했더니 #N/A가 뜸 같은 문제는 초보 때 정말 자주 만납니다. 특히 주문표에 상품코드를 입력하면 단가표에서 단가를 가져오고, 수량과 곱해서 금액까지 계산하는 파일에서 많이 생깁니다.

오늘은 아주 작은 주문표 하나를 끝까지 완성해 보겠습니다. 핵심은 VLOOKUP 수식 자체보다, 단가표 범위를 어디부터 어디까지 잡는지, 그리고 아래로 복사해도 범위가 움직이지 않게 고정하는지입니다. 이 부분만 잡히면 실무 주문서, 견적서, 발주서에서 바로 써먹을 수 있습니다.

상황: 상품코드를 입력하면 단가와 금액이 자동으로 나오게 만들기

아래처럼 한 시트 안에 주문 입력표와 단가표가 같이 있다고 가정하겠습니다. 주문 입력표는 A열부터 F열까지, 단가표는 H열부터 J열까지 있습니다.

주문 입력표의 데이터는 3행부터 시작하고, 실제 주문은 4행부터 9행까지 입력합니다. 단가표는 H3:J8 범위에 있고, 상품코드를 기준으로 단가를 찾아올 예정입니다.

예시 데이터는 이렇게 넣어 보겠습니다.

오른쪽 단가표는 H3:J8에 준비합니다.

먼저 E4 셀에 단가를 가져오는 VLOOKUP 수식 입력

단가는 주문 입력표의 E4 셀에 넣습니다. E4는 첫 번째 주문 행의 단가 자리입니다. 여기서 B4에 있는 상품코드 P001을 단가표 H4:J8에서 찾아, 세 번째 열인 단가를 가져오면 됩니다.

=VLOOKUP(B4,$H$4:$J$8,3,FALSE)

이 수식을 말로 풀면 이렇습니다. B4 값을, H4:J8 단가표에서 찾고, 그 범위 안의 3번째 열 값을 가져오며, 상품코드가 정확히 같을 때만 찾겠다는 뜻입니다.

여기서 초보자가 가장 헷갈리는 부분은 3번째 열입니다. 엑셀 전체의 C열이 아니라, 내가 선택한 범위 H4:J8 안에서 몇 번째 열인지를 세야 합니다. H열이 1번째, I열이 2번째, J열이 3번째입니다. 그래서 단가가 들어 있는 J열을 가져오려면 3을 씁니다.

왜 $H$4:$J$8처럼 달러 표시를 붙일까?

VLOOKUP 수식을 E4에만 쓸 때는 H4:J8이라고 입력해도 결과가 잘 나올 수 있습니다. 그런데 실무에서는 E4 하나만 계산하고 끝나지 않습니다. E5, E6, E7 아래 행까지 수식을 복사해야 합니다.

이때 단가표 범위를 고정하지 않으면 문제가 생깁니다. 예를 들어 E4에 아래처럼 입력했다고 해보겠습니다.

=VLOOKUP(B4,H4:J8,3,FALSE)

이 수식을 E5로 복사하면 엑셀이 자동으로 이렇게 바꿉니다.

=VLOOKUP(B5,H5:J9,3,FALSE)

찾을 상품코드가 B5로 바뀌는 것은 맞습니다. 주문 행이 내려갔으니까요. 하지만 단가표 범위까지 H5:J9로 내려가면 안 됩니다. 단가표는 계속 H4:J8을 봐야 하는데, 범위가 한 칸 내려가면서 P001 행이 빠지고 빈 행이 끼어들 수 있습니다.

그래서 단가표 범위에는 달러 표시를 붙입니다. $H$4:$J$8은 H4부터 J8까지의 범위를 움직이지 말라는 뜻입니다. 키보드로 수식을 입력하다가 H4:J8 부분을 선택한 뒤 F4 키를 누르면 달러 표시가 붙습니다.

금액은 F4 셀에서 수량 × 단가로 계산

단가가 E4에 들어왔다면, 금액은 F4 셀에 계산합니다. 수량은 D4, 단가는 E4에 있으므로 곱하면 됩니다.

=D4*E4

이제 E4와 F4를 선택한 뒤, 셀 오른쪽 아래의 작은 네모를 잡고 9행까지 아래로 끌어내리면 됩니다. 또는 E4:F4 범위를 선택하고 복사한 다음 E5:F9에 붙여넣어도 됩니다.

정상이라면 P001은 1,200원, P003은 2,500원, P002는 800원, P004는 1,500원이 들어옵니다. 금액은 각각 수량과 곱해져 계산됩니다.

#N/A가 뜨면 수식보다 상품코드부터 확인

VLOOKUP에서 #N/A는 대부분 “찾는 값이 단가표에 없다”는 뜻입니다. 그런데 실제 파일을 보면 단가표에 분명히 있는 코드인데도 #N/A가 뜰 때가 있습니다. 이때는 수식만 계속 고치기보다 상품코드를 먼저 확인하는 것이 빠릅니다.

가장 흔한 원인은 공백입니다. 주문표 B열에 P003처럼 보이지만 실제로는 뒤에 공백이 붙어 P003 일 수 있습니다. 사람 눈에는 거의 안 보이지만 엑셀은 다른 값으로 봅니다.

두 번째는 코드 형식 차이입니다. 어떤 파일에서는 상품코드가 숫자처럼 들어오고, 어떤 파일에서는 문자처럼 들어옵니다. 이번 예제처럼 P001 형태는 문자라서 비교적 안전하지만, 00123 같은 코드는 앞자리 0이 사라지면서 문제가 생기기 쉽습니다.

초보자라면 우선 단가표의 상품코드와 주문표의 상품코드를 직접 한 번 복사해서 비교해 보세요. 같은 코드처럼 보이는데도 안 찾는다면, 양쪽 셀에 불필요한 공백이 있는지 확인하는 것이 먼저입니다.

보기 좋게 만들려면 IFERROR로 빈칸 처리

주문표를 다른 사람에게 공유할 때 #N/A가 그대로 보이면 파일이 고장 난 것처럼 보일 수 있습니다. 이럴 때는 IFERROR를 함께 쓰면 됩니다. IFERROR는 수식 결과가 오류일 때 대신 보여줄 값을 정하는 함수입니다.

E4 셀의 수식을 아래처럼 바꿔 보겠습니다.

=IFERROR(VLOOKUP(B4,$H$4:$J$8,3,FALSE),"")

이 수식은 VLOOKUP으로 단가를 찾되, 상품코드가 없어서 오류가 나면 빈칸으로 표시하라는 뜻입니다. 빈칸 대신 “코드확인”이라고 표시하고 싶다면 아래처럼 바꿔도 됩니다.

=IFERROR(VLOOKUP(B4,$H$4:$J$8,3,FALSE),"코드확인")

다만 실무에서는 오류를 완전히 숨기는 것보다 “코드확인”처럼 표시하는 편이 더 안전할 때가 많습니다. 빈칸으로 두면 단가가 아직 입력되지 않은 것인지, 코드가 잘못된 것인지 구분하기 어렵기 때문입니다.

VLOOKUP 수식에서 자주 틀리는 부분만 따로 정리

이번 예제에서 수식이 안 맞을 때는 아래 순서로 확인하면 됩니다. 복잡해 보이지만 실제로는 대부분 이 안에서 해결됩니다.

특히 마지막의 FALSE는 꼭 기억해 두면 좋습니다. VLOOKUP의 마지막 인수에 FALSE를 넣으면 상품코드가 정확히 같은 경우만 찾습니다. 주문서나 정산표처럼 코드가 기준인 파일에서는 보통 FALSE를 넣는 것이 안전합니다.

상품명도 자동으로 가져오고 싶다면?

이번 예제에서는 C열 상품명을 사람이 직접 입력해 둔 상태로 시작했습니다. 하지만 상품코드를 입력하면 상품명도 자동으로 나오게 만들 수 있습니다. 이때는 같은 단가표에서 두 번째 열을 가져오면 됩니다.

C4 셀에 아래 수식을 입력합니다.

=IFERROR(VLOOKUP(B4,$H$4:$J$8,2,FALSE),"코드확인")

단가를 가져올 때는 세 번째 열이라서 3을 썼고, 상품명은 두 번째 열이라서 2를 씁니다. 이렇게 해두면 B열 상품코드만 입력해도 C열 상품명과 E열 단가가 자동으로 채워집니다.

실무에서는 이 방식이 훨씬 안전합니다. 상품명을 사람이 직접 입력하면 “파일철”, “파일 철”, “화일철”처럼 표기가 달라질 수 있습니다. 상품코드 하나를 기준으로 상품명과 단가를 가져오면 입력 실수를 줄일 수 있습니다.

XLOOKUP을 쓸 수 있다면 조금 더 읽기 쉬운 수식도 가능

Microsoft 365 또는 최신 버전 엑셀을 사용한다면 XLOOKUP 함수도 많이 씁니다. VLOOKUP보다 구조가 직관적이라서, 찾을 범위와 가져올 범위를 따로 지정할 수 있습니다.

E4 셀에 단가를 가져오는 XLOOKUP 수식은 아래와 같습니다.

=IFERROR(XLOOKUP(B4,$H$4:$H$8,$J$4:$J$8),"코드확인")

뜻은 간단합니다. B4 값을 H4:H8 상품코드 목록에서 찾고, 같은 위치의 J4:J8 단가를 가져오라는 뜻입니다. 다만 회사나 거래처 파일은 아직 VLOOKUP 기준으로 만들어진 경우도 많으니, 초보 단계에서는 VLOOKUP 구조를 먼저 익혀 두는 편이 좋습니다.

이번 예제를 내 파일에 적용할 때 바꿔야 하는 셀 주소

내 파일에 적용할 때는 수식을 통째로 외우기보다, 각 위치가 무엇을 의미하는지 보고 바꾸면 됩니다. 이번 예제의 핵심 수식은 아래입니다.

=IFERROR(VLOOKUP(B4,$H$4:$J$8,3,FALSE),"코드확인")

B4는 주문표에서 상품코드가 들어 있는 셀입니다. 내 파일에서 상품코드가 C5에 있다면 B4 대신 C5를 넣어야 합니다.

$H$4:$J$8은 단가표 전체 범위입니다. 단, 이 범위의 첫 번째 열에는 반드시 찾을 기준인 상품코드가 있어야 합니다. 단가표가 M열부터 O열까지 있다면 $M$4:$O$100처럼 바꾸면 됩니다.

3은 선택한 단가표 범위 안에서 가져올 열 번호입니다. 범위가 M:O라면 M이 1번째, N이 2번째, O가 3번째입니다. 가져올 값이 어느 열에 있는지 범위 안에서 다시 세어 보세요.

작은 주문표가 견적서와 발주서로 커지는 방식

오늘 만든 구조는 단순하지만 활용 범위가 넓습니다. 상품코드를 기준으로 상품명, 규격, 단가, 과세구분까지 가져오면 작은 견적서 양식이 됩니다. 수량을 입력하고 금액을 계산하면 발주서나 출고 요청서에도 그대로 응용할 수 있습니다.

처음에는 VLOOKUP의 괄호와 쉼표가 낯설 수 있습니다. 그래도 찾을 값 → 단가표 범위 → 가져올 열 번호 → 정확히 찾기 순서만 기억하면 훨씬 덜 헷갈립니다. 다음에는 여기에 SUMIF나 SUMIFS를 붙여서 상품별 주문금액 합계까지 이어서 만들면 좋습니다.