엑셀 VLOOKUP 함수 사용법｜제품코드로 제품명과 단가 가져오기

엑셀로 주문서나 실적표를 작성하다 보면 다른 표에 있는 정보를 가져와야 할 때가 많습니다.

제품코드를 보고 제품명과 단가를 입력하거나, 사번으로 직원 이름을 찾고, 거래처코드에 맞는 담당자를 확인하는 작업이 대표적입니다.

항목이 몇 개라면 직접 찾아 입력할 수 있지만, 데이터가 많아질수록 시간이 오래 걸리고 잘못 옮겨 적을 가능성도 커집니다.

이럴 때 사용하는 함수가 VLOOKUP입니다.

이번 글에서는 제품코드를 기준으로 제품명과 단가를 가져오는 예제를 통해 VLOOKUP의 기본 구조와 정확히 일치하는 값 찾기, 수식을 복사할 때 범위를 고정하는 방법까지 알아보겠습니다.

※ 예제 수식은 Excel 2016과 Microsoft 365를 비롯한 이후 버전에서 사용할 수 있습니다.

VLOOKUP은 어떤 함수인가?

VLOOKUP은 지정한 표의 첫 번째 열에서 기준값을 찾고, 같은 행에 있는 다른 열의 값을 가져오는 함수입니다.

예를 들어 제품 기준표가 다음과 같다고 가정해 보겠습니다.

제품코드	제품명	단가
P001	제품 A	8,500
P002	제품 B	12,000
P003	제품 C	15,000
P004	제품 D	7,000
P005	제품 E	9,500
P006	제품 F	11,000

제품코드가 P003이면 같은 행에 있는 제품명 ‘제품 C’와 단가 15,000원을 가져올 수 있습니다.

이처럼 VLOOKUP은 공통된 기준값으로 두 표의 정보를 연결할 때 유용합니다. Microsoft 공식 VLOOKUP 안내

1. VLOOKUP 함수의 기본 구조

기본 구조는 다음과 같습니다.

=VLOOKUP(찾을 값,참조할 표 범위,가져올 열 번호,일치 방식)

각 항목을 예제에 맞춰 설명하면 다음과 같습니다.

항목	의미	예제
찾을 값	조회 기준이 되는 값	G3에 입력한 제품코드
참조할 표 범위	기준값과 가져올 정보가 있는 범위	B3:D8
가져올 열 번호	선택한 범위에서 몇 번째 열을 가져올지 지정	제품명 2, 단가 3
일치 방식	정확히 일치하는 값을 찾을지 지정	FALSE

이번 글에서는 제품코드가 정확히 일치하는 정보를 가져오므로 마지막 항목에 FALSE를 사용합니다.

2. 제품코드로 제품명 가져오기

앞의 제품 기준표를 B2 셀부터 입력했다고 가정해 보겠습니다.

• B2:D2: 제목 행
• B3:B8: 제품코드
• C3:C8: 제품명
• D3:D8: 단가

오른쪽에는 조회표를 만듭니다.

• G2: 조회 코드
• H2: 제품명
• I2: 단가
• G3: P003

H3 셀에 다음 수식을 입력합니다.

=VLOOKUP(G3,$B$3:$D$8,2,FALSE)

수식을 순서대로 읽으면 다음과 같습니다.

G3에 입력된 제품코드를 기준표의 첫 번째 열에서 찾고, 같은 행의 두 번째 열에 있는 값을 가져와 줘.

G3에 P003이 입력되어 있으므로 H3에는 ‘제품 C’가 표시됩니다.

수식에 있는 $B$3:$D$8은 수식을 복사해도 기준표 범위가 바뀌지 않도록 고정한 표시입니다. 범위를 고정하는 방법은 뒤에서 자세히 설명하겠습니다.

3. 열 번호를 바꿔 단가 가져오기

이번에는 같은 제품코드로 단가를 가져오겠습니다.

I3 셀에 다음 수식을 입력합니다.

=VLOOKUP(G3,$B$3:$D$8,3,FALSE)

제품명을 가져올 때와 달라진 부분은 열 번호입니다.

• 제품명: 2
• 단가: 3

P003의 단가는 15,000원이므로 I3에는 15,000이 표시됩니다.

여기서 열 번호는 엑셀 전체의 열 순서가 아니라, 선택한 표 범위 안에서의 순서입니다.

이번 예제의 범위는 B3:D8이므로 다음과 같이 셉니다.

실제 열	선택한 범위 안의 열 번호	내용
B열	1	제품코드
C열	2	제품명
D열	3	단가

D열이라고 해서 열 번호에 4를 입력하면 안 됩니다. 선택한 범위 안에서는 세 번째 열이므로 3을 입력해야 합니다.

4. 제품코드를 조회할 때 FALSE를 사용하는 이유

VLOOKUP의 마지막 항목은 일치 방식을 지정합니다.

입력값	의미
FALSE 또는 0	정확히 일치하는 값 찾기
TRUE 또는 1	근사 일치 방식으로 찾기
생략	TRUE와 동일하게 처리

제품코드나 사번처럼 식별값이 정확히 맞아야 하는 조회에는 FALSE를 지정합니다.

=VLOOKUP(G3,$B$3:$D$8,2,FALSE)

다음처럼 0을 입력해도 같습니다.

=VLOOKUP(G3,$B$3:$D$8,2,0)

마지막 항목을 생략하면 근사 일치 방식으로 작동합니다. 이 방식은 기준표의 첫 번째 열이 오름차순으로 정렬되어 있어야 하며, 정렬되지 않은 표에서는 의도하지 않은 결과가 나올 수 있습니다.

 

이번처럼 정확한 제품코드를 조회할 때는 마지막 항목에 FALSE를 명시하는 습관을 들이는 것이 좋습니다.

5. 여러 제품을 조회할 때 범위 고정하기

주문 목록에 제품코드가 여러 개 있다면 수식을 아래로 복사해 한 번에 조회할 수 있습니다.

G3:G6에 다음 코드를 입력해 보겠습니다.

조회 코드

P003

P001

P006

P002

H3과 I3에 입력한 수식은 다음과 같습니다.

제품명:

=VLOOKUP(G3,$B$3:$D$8,2,FALSE)

단가:

=VLOOKUP(G3,$B$3:$D$8,3,FALSE)

H3:I3을 함께 선택한 뒤, 선택 영역 오른쪽 아래의 작은 사각형인 채우기 핸들을 아래로 끌어 H6:I6까지 수식을 채웁니다.

결과는 다음과 같습니다.

조회 코드	제품명	단가
P003	제품 C	15,000
P001	제품 A	8,500
P006	제품 F	11,000
P002	제품 B	12,000

수식을 아래로 복사하면 조회 코드의 셀 주소는 행에 맞춰 바뀝니다.

• H3에서는 G3 조회
• H4에서는 G4 조회
• H5에서는 G5 조회
• H6에서는 G6 조회

반면 기준표 범위인 $B$3:$D$8은 그대로 유지됩니다.

달러 기호가 없으면 어떻게 될까?

다음처럼 범위를 고정하지 않은 수식을 아래로 복사하면 참조 범위도 함께 이동합니다.

=VLOOKUP(G3,B3:D8,2,FALSE)

한 행 아래로 복사한 수식:

=VLOOKUP(G4,B4:D9,2,FALSE)

기준표의 첫 번째 데이터가 있는 3행이 조회 범위에서 빠지는 것입니다.

이번 예제에서 G4의 P001은 B3에 있으므로, 범위가 B4:D9로 이동하면 찾을 수 없게 됩니다.

이처럼 복사한 수식에서 일부 값만 조회되지 않는다면 기준표 범위를 고정했는지 확인해야 합니다.

F4로 범위 고정하기

Windows용 Excel에서는 수식을 편집하면서 B3:D8 부분을 선택하고 F4를 누르면 $B$3:$D$8 형태로 바꿀 수 있습니다.

달러 기호를 직접 입력해도 됩니다. 노트북 설정에 따라 Fn과 F4를 함께 눌러야 할 수도 있습니다.

수식을 아래로 복사할 때 조회할 셀은 행에 따라 바뀌고, 기준표는 고정되도록 구성하는 것이 핵심입니다. Microsoft 공식 셀 참조 안내

6. VLOOKUP 사용 시 자주 확인할 문제

기준값이 선택한 범위의 첫 번째 열에 있는가?

VLOOKUP은 지정한 표 범위의 첫 번째 열에서 기준값을 찾습니다.

이번 예제에서는 제품코드가 B열에 있으므로 범위도 B열부터 시작해야 합니다.

$B$3:$D$8

C3:D8만 선택하면 첫 번째 열이 제품명 열이 되므로 제품코드로 조회할 수 없습니다.

표에 없는 코드를 입력했는가?

G3에 기준표에 없는 P999를 입력하면, 이번 예제의 수식은 #N/A를 표시합니다.

=VLOOKUP(G3,$B$3:$D$8,2,FALSE)

이때는 제품코드를 잘못 입력했는지, 새 제품이 기준표에 등록되지 않았는지, 조회 범위에서 해당 행이 빠졌는지 확인합니다.

기준표에 새 제품을 추가하더라도 $B$3:$D$8이라는 범위가 새 행까지 자동으로 늘어나는 것은 아닙니다. 데이터가 늘어나면 수식의 조회 범위도 확인해야 합니다.

공백이나 데이터 형식이 다른가?

기준표와 조회 목록에 같은 코드가 보이더라도 앞뒤 공백이 있으면 조회가 되지 않을 수 있습니다.

숫자형 코드에서는 한쪽이 숫자이고 다른 쪽이 문자로 저장되어 있는지도 확인해야 합니다.

특히 00123처럼 앞의 0이 중요한 코드는 두 표에서 동일한 형태로 관리하는 것이 좋습니다.

같은 코드가 여러 번 있는가?

정확히 일치하는 코드가 여러 개라면 VLOOKUP은 위에서부터 처음 일치하는 행의 값을 가져옵니다.

따라서 같은 제품코드에 서로 다른 단가가 등록되어 있으면 원하는 단가가 조회되지 않을 수 있습니다.

제품별 기준정보를 관리하는 표에서는 코드가 중복되지 않는지 확인해야 합니다.

가져올 열 번호가 표 범위를 벗어났는가?

B3:D8은 총 세 개의 열로 구성되어 있습니다.

여기에서 열 번호에 4를 입력하면 범위를 벗어나므로 #REF! 오류가 발생합니다.

조회하려는 열이 참조 범위에 포함되어 있는지와 열 번호를 함께 확인합니다.

VLOOKUP과 조건별 집계 함수는 역할이 다르다

앞서 다룬 엑셀 SUMIF·COUNTIF 함수 사용법｜조건에 맞는 합계와 개수 구하기에서는 특정 조건에 해당하는 금액을 합산하거나 건수를 세는 방법을 알아봤습니다.

이번에 배운 VLOOKUP은 제품코드처럼 공통된 기준값으로 제품명이나 단가를 가져올 때 사용합니다.

하고 싶은 작업	사용할 함수
제품코드로 제품명이나 단가 가져오기	VLOOKUP
특정 제품의 주문 금액 합산하기	SUMIF
특정 제품의 주문 건수 세기	COUNTIF
특정 제품이면서 완료된 주문 금액 합산하기	SUMIFS

주문 내역에 같은 제품코드가 여러 번 나온다고 해서 VLOOKUP이 해당 제품의 주문 금액을 모두 더해 주는 것은 아닙니다.

먼저 필요한 작업이 ‘정보 조회’인지 ‘합계·개수 계산’인지 구분하면 함수를 선택하기 쉬워집니다.

핵심 내용 정리

VLOOKUP의 기본 구조는 다음과 같습니다.

=VLOOKUP(찾을 값,참조할 표 범위,가져올 열 번호,일치 방식)

제품명 가져오기:

=VLOOKUP(G3,$B$3:$D$8,2,FALSE)

단가 가져오기:

=VLOOKUP(G3,$B$3:$D$8,3,FALSE)

이번 예제에서 기억할 부분은 네 가지입니다.

• 조회 기준인 제품코드는 선택한 표의 첫 번째 열에 둡니다.
• 가져올 열 번호는 선택한 범위 안에서 1부터 셉니다.
• 정확한 코드 조회에는 FALSE를 지정합니다.
• 수식을 복사할 때 기준표가 움직이지 않도록 범위를 고정합니다.

제품명이나 단가를 일일이 찾아 입력하던 작업도 공통된 기준값과 기준표가 정리되어 있으면 수식으로 연결할 수 있습니다.

다음 글에서는 IFERROR 함수를 활용해 조회 과정에서 발생하는 오류를 어떻게 표시하고 관리할지 알아보겠습니다.