저는 맥과 아이패드에서 문서 작업을 하고, 엑셀은 견적서나 일정처럼 표가 필요한 업무에도 활용합니다. 상품명과 금액을 매번 찾아 옮기다 보면 한 줄이 어긋나거나 이전 단가가 남기 쉽습니다.
이런 작업에 쓰는 함수가 엑셀 VLOOKUP입니다. 상품코드 하나를 입력하면 다른 표에서 같은 코드를 찾아 상품명이나 단가를 가져옵니다. 긴 수식을 외우기보다, 어느 값을 어느 표에서 찾는지 이해하면 브이룩업 사용법이 한결 쉬워집니다.
먼저 알아둘 두 표의 관계
예제의 왼쪽은 주문 내용을 적는 표이고, 오른쪽 G6:I10은 상품 목록입니다. G열에는 상품코드, H열에는 상품명, I열에는 단가가 있습니다. 여기에 나온 상품과 가격은 학습용 가상 데이터이며 실제 판매 조건과는 관계없습니다.
노란 칸에는 상품코드와 수량을 입력하고, 초록 칸에는 수식을 넣습니다. 주문서와 상품 목록의 나열 순서는 달라도 됩니다. 같은 코드로 찾기 때문입니다.
다만 찾을 코드가 조회 범위의 맨 왼쪽 열에 있어야 합니다. G6:I10을 범위로 잡았다면 G열에서 검색을 시작하고 H열이나 I열의 값을 가져옵니다. 상품명이 왼쪽, 코드가 오른쪽인 표라면 열 배치를 바꾸거나 다른 조회 함수를 써야 합니다.
상품코드로 상품명 찾는 순서
먼저 A6에 P1003을 입력합니다. 다음으로 결과가 들어갈 B6을 선택하고 =VLOOKUP(A6,$G$6:$I$10,2,FALSE)를 입력한 뒤 Enter를 누릅니다. 그러면 B6에는 무선 마우스가 나옵니다.
수식의 네 항목은 쉼표로 구분합니다. 위 수식을 나누어 보면 다음과 같습니다.
수식 항목
뜻
예제에서 하는 일
A6
찾을 값
A6에 입력한 P1003을 찾습니다.
$G$6:$I$10
조회 범위
오른쪽 상품 목록에서 찾습니다.
2
가져올 열 번호
범위의 두 번째 열인 H열의 상품명을 가져옵니다.
FALSE
정확히 일치
P1003과 같은 코드만 찾습니다.
열 번호 2는 엑셀 전체의 B열을 뜻하지 않습니다. 지정한 범위 안에서 G열이 첫 번째, H열이 두 번째, I열이 세 번째입니다.
상품코드나 사번처럼 한 항목을 정확하게 찾아야 할 때는 FALSE 또는 0을 씁니다. 마지막 항목을 생략하면 TRUE로 처리됩니다. TRUE는 비슷한 글자를 찾아주는 기능이 아니라 오름차순으로 정렬된 표에서 구간에 맞는 값을 찾는 방식이라, 상품명 조회에 무심코 쓰면 곤란합니다.
단가 조회와 범위 고정
C6에는 =VLOOKUP(A6,$G$6:$I$10,3,FALSE)를 입력합니다. 이번에는 I열의 단가인 35,000원을 가져옵니다. D6의 수량이 2라면 E6의 =C6*D6으로 금액 70,000원을 계산할 수 있습니다.
다음 주문에도 같은 계산을 적용하려면 B6:C6의 채우기 핸들을 10행까지 아래로 끌어 복사합니다. 금액 계산이 있는 E6도 E10까지 채웁니다. 수량은 D열에 직접 적으며, 결과 셀에 상품명이나 가격을 다시 입력하면 수식이 지워지므로 입력 칸과 구분해야 합니다.
이때 $가 중요합니다. 찾을 값은 다음 주문으로 바뀌어야 하지만 상품 목록은 그대로여야 합니다.
참조 방식
한 줄 아래로 복사
결과
A6
A7
다음 주문의 코드를 읽습니다.
$G$6:$I$10
$G$6:$I$10
같은 상품 목록을 유지합니다.
G6:I10
G7:I11
목록이 밀려 첫 상품이 빠집니다.
첫 줄의 코드만 P1002로 바꾸면 상품명은 노트북 거치대, 단가는 28,000원으로 바뀝니다. 수량 2를 유지하면 금액은 56,000원입니다. 이름과 금액을 일일이 수정할 필요가 줄어듭니다.
#N/A가 나올 때 확인할 내용
오류연습 시트의 P9999는 상품 목록에 없는 코드라 #N/A가 나옵니다. 같은 1001처럼 보여도 A8은 문자, A9는 숫자입니다. 아래 순서로 원인을 살펴보면 됩니다.
살펴볼 부분
오류가 나는 이유
해결 방법
코드 누락
목록에 P9999가 없음
등록된 코드인지 확인합니다.
앞뒤 공백
눈에 보이지 않는 공백 포함
양쪽 코드의 불필요한 공백을 지웁니다.
문자와 숫자
문자 1001과 숫자 1001이 다름
양쪽 값의 자료형을 맞춥니다.
조회 범위
복사하면서 목록 범위가 이동
$로 상품 목록을 고정합니다.
셀 표시 형식만 바꾸는 것으로 기존 문자 값이 숫자로 변환되지는 않습니다.
0012처럼 앞의 0도 의미가 있는 코드는 양쪽을 같은 문자 형식으로 관리하는 편이 낫습니다. 무조건 숫자로 바꾸면 서로 다른 코드를 같은 값으로 취급할 수 있습니다.
목록에 없는 값만 안내 문구로 바꾸려면 IFNA로 감쌀 수 있습니다. C7의 =IFNA(VLOOKUP(A7,'상품조회'!$G$6:$I$10,2,FALSE),"코드 확인")은 #N/A 대신 코드 확인을 보여줍니다. A7에 등록된 코드를 입력하면 다시 해당 상품명이 나옵니다.
IFERROR도 자주 쓰지만 참조 오류나 잘못된 열 번호에서 생긴 오류까지 가립니다. 무조건 빈칸이나 0으로 바꾸면 단가가 없는 상품이 무료인 것처럼 보일 수도 있습니다. 오류의 원인을 먼저 살핀 뒤, 못 찾은 코드에 어떤 문구를 보여줄지 정하는 것이 좋습니다.
엑셀 VLOOKUP 사용법 결론
같은 코드가 상품 목록에 여러 번 있으면 정확히 일치하는 검색도 첫 번째 일치 항목을 가져옵니다. 중복 행의 금액을 모두 더해주는 함수는 아닙니다. 상품 목록의 코드는 되도록 하나씩 관리하고, 주문별 합계가 목적이라면 SUMIFS 같은 합계 함수를 구분해서 써야 합니다.
새 문서를 만드는 환경이라면 XLOOKUP도 고려할 수 있습니다. 조회 열과 결과 열을 따로 지정하고 왼쪽 값도 가져올 수 있습니다. 다만 Excel 2016과 2019에서는 지원하지 않으므로 파일을 주고받는 상대의 버전도 중요합니다. 기존 문서에 브이룩업이 들어 있다면 그 구조를 이해하는 일이 여전히 필요합니다.
엑셀 VLOOKUP을 처음 익힐 때는 찾을 값, 조회 범위, 가져올 열, 정확한 일치 여부부터 확인하면 됩니다. 그다음 $로 범위를 고정하고 코드 누락을 점검하면 됩니다. 작은 견적표부터 적용해 보면 반복 입력을 줄이는 데 도움이 됩니다.
