엑셀 단가 자동 입력 VLOOKUP 안됨? 품목코드로 금액까지 계산하는 초보 체크리스트
엑셀에서 품목코드를 입력하면 품목명과 단가가 자동으로 들어오게 만들고 싶은데, 막상 VLOOKUP 안됨, #N/A 오류, 금액 계산 안됨 같은 문제가 자주 생깁니다. 특히 견적서, 발주서, 청구서처럼 같은 품목을 반복해서 입력하는 파일에서는 단가를 손으로 치면 오타가 나기 쉽고, 나중에 합계가 안 맞는 원인이 됩니다.
이번 글은 초보자도 바로 따라 할 수 있도록 작은 예시 표로 시작하겠습니다. 핵심은 품목코드로 단가표에서 품목명과 단가를 찾아오고, 수량을 곱해서 금액을 계산하는 흐름입니다. 함수는 실무에서 가장 많이 쓰는 VLOOKUP, IF, IFERROR, SUM만 사용합니다.
지금 만들 파일 구조는 어떻게 잡아야 할까?
먼저 같은 시트 안에 입력표와 단가표가 있다고 가정하겠습니다. 왼쪽은 실제로 견적서나 발주서에 입력하는 영역이고, 오른쪽은 품목코드별 단가를 모아둔 기준표입니다.
| 구역 | 셀 범위 | 역할 |
|---|---|---|
| 입력표 | A2:E8 | 품목코드, 품목명, 수량, 단가, 금액 입력 |
| 단가표 | G2:I8 | 품목코드별 품목명과 단가 저장 |
| 합계 셀 | E10 | 금액 합계를 표시 |
예시 데이터는 아래처럼 준비합니다. 실제 파일에서는 A열에 품목코드만 입력하고, B열 품목명과 D열 단가는 자동으로 들어오게 만들 예정입니다.
| A열 | B열 | C열 | D열 | E열 |
|---|---|---|---|---|
| 품목코드 | 품목명 | 수량 | 단가 | 금액 |
| P-1001 | 3 | |||
| P-1003 | 5 | |||
| P-1005 | 2 |
오른쪽 G2:I8에는 아래 단가표를 둡니다. 여기서 중요한 점은 품목코드가 단가표의 가장 왼쪽 열에 있어야 한다는 것입니다. VLOOKUP은 기본적으로 왼쪽 첫 열에서 찾고, 그 오른쪽 값을 가져오는 함수라서 이 구조가 아주 중요합니다.
| G열 | H열 | I열 |
|---|---|---|
| 품목코드 | 품목명 | 단가 |
| P-1001 | 무선마우스 | 12000 |
| P-1002 | 키보드 | 28000 |
| P-1003 | USB허브 | 15000 |
| P-1004 | HDMI케이블 | 7000 |
| P-1005 | 노트북거치대 | 23000 |
품목명은 어느 셀에 어떤 수식을 넣어야 할까?
먼저 B3 셀에 품목명을 자동으로 가져오는 수식을 입력합니다. A3에 있는 품목코드를 G3:I8 단가표에서 찾아서, 두 번째 열인 H열의 품목명을 가져오는 방식입니다.
=IFERROR(VLOOKUP(A3,$G$3:$I$8,2,FALSE),"확인필요")수식에서 A3은 찾을 값입니다. 즉, 입력표에 적은 품목코드입니다. $G$3:$I$8은 단가표 범위인데, 앞에 달린 달러 표시 $는 수식을 아래로 복사해도 범위가 밀리지 않게 고정한다는 뜻입니다.
2는 단가표 범위 안에서 두 번째 열을 가져오겠다는 의미입니다. G열이 1번째, H열이 2번째, I열이 3번째입니다. 마지막 FALSE는 비슷한 값이 아니라 정확히 같은 품목코드만 찾겠다는 뜻입니다.
수식을 B3에 넣었다면 B8까지 아래로 복사합니다. 복사는 B3 셀 오른쪽 아래 작은 네모를 잡고 아래로 끌어도 되고, B3을 복사한 뒤 B4:B8 범위에 붙여넣어도 됩니다.
단가는 품목명과 거의 같은 방식으로 가져오면 된다
D3 셀에는 단가를 가져오는 수식을 입력합니다. 이번에는 단가표의 세 번째 열인 I열 값을 가져와야 하므로 열 번호만 3으로 바꿉니다.
=IFERROR(VLOOKUP(A3,$G$3:$I$8,3,FALSE),"")여기서는 오류가 났을 때 빈칸처럼 보이게 ""를 넣었습니다. 품목명 쪽은 사람이 바로 알아차리도록 “확인필요”라고 표시했고, 단가 쪽은 금액 계산에 방해되지 않도록 빈칸 처리한 것입니다.
초보자분들이 헷갈리는 부분이 바로 이 지점입니다. B3 수식과 D3 수식은 거의 같지만, 가져올 열 번호가 다릅니다. 품목명은 2번, 단가는 3번입니다. 단가가 품목명 자리에 나오거나 품목명이 단가 자리에 나온다면 대부분 이 열 번호를 잘못 적은 경우입니다.
금액 계산은 E3 셀에서 수량과 단가를 곱한다
이제 E3 셀에 금액을 계산합니다. 금액은 수량 곱하기 단가이므로 기본 수식은 =C3*D3입니다. 다만 단가가 비어 있을 때 0이 표시되면 보기 불편할 수 있으니, IF 함수로 한 번 감싸겠습니다.
=IF(D3="","",C3*D3)이 수식은 “D3 단가가 빈칸이면 금액도 빈칸으로 두고, 단가가 있으면 C3 수량과 D3 단가를 곱하라”는 뜻입니다. E3에 입력한 뒤 E8까지 복사하면 각 행의 금액이 자동으로 계산됩니다.
합계는 E10 셀에 표시하겠습니다. E10 셀에는 아래 수식을 입력합니다.
=SUM(E3:E8)SUM은 선택한 범위의 숫자를 모두 더하는 함수입니다. 여기서는 E3부터 E8까지의 금액을 더합니다. 견적서나 발주서에서 가장 마지막에 총액을 확인할 때 자주 쓰는 기본 함수입니다.
VLOOKUP 안됨이 뜰 때 어디부터 봐야 할까?
VLOOKUP에서 가장 자주 만나는 오류는 #N/A입니다. 이 오류는 보통 “찾으려는 값이 단가표에 없다”는 뜻입니다. 하지만 실제 업무 파일에서는 분명히 같은 코드처럼 보이는데도 오류가 나는 경우가 많습니다.
| 확인할 곳 | 오류 원인 | 해결 방법 |
|---|---|---|
| A열 품목코드 | 코드 끝에 공백이 있음 | 다시 입력하거나 공백 제거 |
| G열 단가표 코드 | 단가표에 코드가 없음 | 단가표에 품목 추가 |
| VLOOKUP 범위 | 범위가 G:I 전체가 아니라 일부만 잡힘 | $G$3:$I$8처럼 정확히 고정 |
| 열 번호 | 품목명과 단가 열 번호를 반대로 입력 | 품목명 2, 단가 3 확인 |
| 마지막 옵션 | FALSE를 생략해서 엉뚱한 값이 나옴 | 정확히 찾기는 FALSE 입력 |
특히 마지막 옵션 FALSE는 꼭 기억해두면 좋습니다. 품목코드, 거래처코드, 사번처럼 정확히 일치해야 하는 값은 대부분 FALSE를 씁니다. 이 부분을 생략하면 비슷한 값을 찾아오는 것처럼 보일 수 있어 초보자 입장에서는 더 헷갈립니다.
수식 복사 후 값이 이상하면 이 체크리스트를 보자
수식은 맞게 넣은 것 같은데 아래 행으로 복사하니 일부만 맞고 일부는 틀릴 때가 있습니다. 이때는 수식을 새로 만들기보다, 아래 순서대로 확인하는 편이 빠릅니다.
- 단가표 범위에 $ 표시가 있는지 확인:
$G$3:$I$8처럼 고정되어 있어야 합니다. - A열 품목코드가 실제 단가표에 있는지 확인: 오타 하나만 있어도 찾지 못합니다.
- 코드 모양이 같은지 확인:
P-1001과P–1001처럼 하이픈 모양이 다르면 다른 값입니다. - 수량 C열이 숫자인지 확인: 왼쪽 정렬된 숫자는 텍스트일 수 있습니다.
- 단가 D열이 숫자인지 확인: 단가에 쉼표나 원 표시를 직접 입력하면 계산이 꼬일 수 있습니다.
실무에서는 단가를 12,000원처럼 직접 입력하는 경우가 있습니다. 보기에는 편하지만 계산할 때 문자로 인식될 수 있습니다. 단가는 숫자 12000으로 입력하고, 표시 형식에서 쉼표나 원 표시를 적용하는 것이 안전합니다.
XLOOKUP을 쓸 수 있다면 이렇게도 가능하다
Microsoft 365 또는 Excel 2021 이후 버전에서는 XLOOKUP을 사용할 수 있습니다. 다만 초보자라면 먼저 VLOOKUP 구조를 이해하는 것이 좋고, 그다음 XLOOKUP을 보면 훨씬 쉽게 느껴집니다.
B3 셀의 품목명 수식은 아래처럼 쓸 수 있습니다.
=IFERROR(XLOOKUP(A3,$G$3:$G$8,$H$3:$H$8),"확인필요")D3 셀의 단가 수식은 아래처럼 쓸 수 있습니다.
=IFERROR(XLOOKUP(A3,$G$3:$G$8,$I$3:$I$8),"")XLOOKUP은 찾을 범위와 가져올 범위를 따로 지정합니다. 그래서 단가표에서 몇 번째 열인지 세지 않아도 되는 장점이 있습니다. 하지만 회사 파일에서 아직 VLOOKUP을 많이 쓰는 경우가 많으니, 두 방식을 모두 알아두면 다른 사람이 만든 파일을 볼 때도 도움이 됩니다.
실무에서 조금 더 편하게 쓰는 응용 팁
품목코드를 입력하지 않은 행에는 품목명 칸에 “확인필요”가 뜨는 것이 거슬릴 수 있습니다. 이럴 때는 A3이 빈칸이면 아무것도 표시하지 않고, 코드가 입력되었을 때만 찾도록 수식을 조금 바꾸면 됩니다.
=IF(A3="","",IFERROR(VLOOKUP(A3,$G$3:$I$8,2,FALSE),"확인필요"))단가도 같은 방식으로 바꿀 수 있습니다.
=IF(A3="","",IFERROR(VLOOKUP(A3,$G$3:$I$8,3,FALSE),""))이렇게 해두면 빈 행은 깔끔하게 비어 있고, 잘못된 품목코드를 입력했을 때만 확인이 필요한 표시가 나타납니다. 발주 품목이 매번 3개일 수도 있고 20개일 수도 있는 양식에서는 이런 처리 하나가 파일을 훨씬 보기 좋게 만듭니다.
적용 전에 확인할 것
품목코드로 단가를 자동 입력하는 파일은 구조만 잘 잡아두면 매일 쓰는 양식이 됩니다. A열에는 품목코드, C열에는 수량만 입력하고, B열 품목명과 D열 단가, E열 금액은 수식으로 처리하는 방식입니다.
마지막으로 세 가지만 꼭 확인해보세요. 첫째, 단가표에서 품목코드가 가장 왼쪽 열에 있는지 확인합니다. 둘째, VLOOKUP 범위는 $G$3:$I$8처럼 고정합니다. 셋째, 정확히 일치하는 코드를 찾을 때는 마지막에 FALSE를 넣습니다.
이 세 가지가 맞으면 대부분의 VLOOKUP 안됨, 단가 자동 입력 오류, 금액 합계 안 맞음 문제는 훨씬 쉽게 잡을 수 있습니다. 처음에는 수식이 길어 보여도, 셀 주소가 어떤 역할을 하는지만 알면 실제 업무 파일에 그대로 옮겨 쓰기 어렵지 않습니다.