VLOOKUP 완전정복 — 엑셀에서 값을 찾아오는 가장 유명한 함수
VLOOKUP이 값을 돌려줬다는 사실만으로 올바른 단가를 찾았다고 볼 수는 없습니다. 조회 키, 반환 열, 일치 방식이 모두 맞아야 합니다. 이 글은 제품코드 P003으로 단가 89000을 찾는 가상 입력으로 주문표를 만들고, 다른 코드와 미등록 코드까지 바꾸어 검산합니다. 원본 회사 데이터나 실제 주문은 사용하지 않습니다.
1. 같은 입력으로 시작하기
마스터 시트 A1:C5에 아래 표를 입력합니다. 제품코드는 텍스트, 단가는 쉼표 없는 숫자로 넣고 표시 형식만 #,##0으로 바꿉니다.
| 제품코드 | 제품명 | 단가 |
|---|---|---|
| P001 | 노트북 | 1200000 |
| P002 | 모니터 | 350000 |
| P003 | 키보드 | 89000 |
| P004 | 마우스 | 45000 |
주문 시트 A1:F5는 아래처럼 만듭니다. C열·E열·F열은 다음 절의 수식으로 채웁니다. D열은 예제 원본의 해당 제품 행을 눈으로 찾아 적는 대조용 값입니다. 원본 단가를 수정했다면 D열의 고정 대조값을 다시 확인해 갱신하세요.
| 제품코드 | 수량 | 조회단가 | 대조단가 | 확인 | 금액 |
|---|---|---|---|---|---|
| P003 | 2 | 수식 | 89000 | 수식 | 수식 |
| P001 | 1 | 수식 | 1200000 | 수식 | 수식 |
| P004 | 3 | 수식 | 45000 | 수식 | 수식 |
| P999 | 1 | 수식 | 빈 셀 | 수식 | 수식 |
2. 정확 일치와 범위 고정
Microsoft 문서에 따르면 FALSE는 표 범위의 첫 번째 열에서 정확히 일치하는 값을 찾습니다. TRUE이거나 네 번째 인수를 생략하면 근사 일치 방식이며 첫 열의 정렬 조건을 확인해야 합니다. 제품코드 예제에서는 FALSE를 명시합니다.
주문!C2에 다음 수식을 입력하고 C5까지 복사합니다.
=IFNA(VLOOKUP(A2,마스터!$A$2:$C$5,3,FALSE),"미등록")여기서 3은 표 범위 안의 세 번째 열인 단가를 뜻합니다. $A$2:$C$5는 고정하고 조회 키 A2는 행마다 바뀌도록 둡니다. C2:C5의 예상 결과는 89000, 1200000, 45000, 미등록입니다. 미등록을 나타내는 셀을 0원 셀로 바꾸지 말고 문자로 남겨 두세요. 그래야 단가가 0인 행과 조회하지 못한 행을 구분할 수 있습니다.
3. 키에 맞는 원본 행으로 검산하기
조회 결과를 항상 마스터!C2와 비교하면 P001 이외의 제품을 잘못 판정합니다. 주문 P003의 원본은 마스터 4행이고 단가는 89000입니다. P001은 2행의 1200000, P004는 5행의 45000입니다. 이처럼 키를 먼저 확인한 다음 그 행의 단가를 D열에 적습니다.
주문!E2의 대조 수식은 다음과 같으며 E5까지 복사합니다.
=IF(C2="미등록","미등록 확인",IF(C2=D2,"일치","불일치"))F2에는 다음 수식을 넣고 F5까지 복사합니다.
=IF(ISNUMBER(C2),B2*C2,"확인 필요")처음 세 주문의 금액은 각각 178000, 1200000, 135000이고 합계는 1513000입니다. 이 합계는 정상적으로 조회된 세 건의 합계입니다. 이 가상 예제의 P999 한 건은 미등록으로 합계에서 제외되어 있습니다. 네 주문을 모두 계산했다고 보고하지 않습니다.
P003의 대조단가 D2를 잠깐 88000으로 바꾸면 E2는 불일치가 되어야 합니다. 89000으로 되돌리면 일치로 복귀해야 합니다. 조회 키를 P002로 바꾸었다면 원본 행을 확인해 대조단가도 350000으로 갱신합니다. 키만 바꾸고 대조값을 그대로 두면 올바른 조회도 불일치로 보입니다.
4. 오류 코드별 원인과 점검
조회값이 다른 원인은 반환 열 번호 오류, 키 형식 불일치, 중복 키처럼 구분해서 확인합니다. 예를 들어 P003의 단가가 89000이 아니면 마스터에서 P003 행을 먼저 찾고, C열 대신 제품명인 B열을 반환한 것은 아닌지 수식을 비교하세요. #N/A만으로 키가 원본에 없다고 단정하지 말고 공백을 포함한 실제 키와 일치 행 수도 확인합니다.
| 관찰한 결과 | 먼저 확인할 곳 | 복구 후 기준 |
|---|---|---|
| #N/A 또는 미등록 | 첫 열에 키가 있는지, 숫자·텍스트와 불필요한 공백 | P003을 넣으면 89000 |
| #REF! | 열 번호가 표 범위의 열 수보다 큰지 | A:C 범위에서 단가 열 번호 3 |
| 오류 없이 다른 값 | FALSE 누락, 잘못된 반환 열, 중복 키 | 키와 해당 행 단가를 대조 |
| 아래로 복사하면 달라짐 | 표 범위의 절대 참조 | 모든 행이 같은 A2:C5 참조 |
Microsoft 문서도 반환 열 번호가 표 범위의 열 수를 넘으면 #REF!가 된다고 설명합니다. 마스터에 열을 삽입했다면 열 번호와 반환 항목을 다시 확인해야 합니다. 숫자 3이 언제나 단가를 뜻하는 것은 아닙니다.
=COUNTIF(마스터!$A$2:$A$5,A2)로 제품코드 일치 행 수도 확인합니다. 이 예제의 등록 제품은 1, P999는 0이어야 합니다. 마스터에서 같은 제품코드가 두 행 이상 발견되면 조회 실습을 중단하고 담당자에게 어느 행을 사용할지 확인하세요. VLOOKUP의 결과 한 개만 보고 중복 데이터가 없다고 추정하지 않습니다.
5. 형식 문제와 복구 순서
P001처럼 문자와 숫자가 섞인 코드는 숫자로 강제 변환하지 않습니다. 앞자리 0이 의미 있는 코드도 보존합니다. 입력 키와 원본 키를 텍스트로 비교하고 앞뒤 공백이나 서로 다른 표기를 확인합니다. TRIM만으로 모든 종류의 공백이나 잘못된 제품명 표기가 해결된다고 가정하지 않습니다.
수식은 A2의 입력값을 바꾸지 않습니다. 조회 결과는 C열에 나오므로 A2가 그대로라는 이유로 조회 실패라고 판정하지 마세요. 계산 결과가 갱신되지 않으면 참조 범위와 계산 설정을 확인하고, 원본을 덮어쓰지 않은 사본에서 같은 네 코드로 다시 재현합니다.
원본 구조가 바뀌면 이전 파일을 보관하고 새 구조의 키 열·반환 열을 적은 뒤 수식을 수정합니다. IFERROR로 모든 오류를 빈칸 처리하면 잘못된 열 번호까지 가려질 수 있습니다. IFNA가 잡는 것은 결과의 #N/A이며, 그 원인까지 미등록으로 판별하는 것은 아닙니다. 등록된 P003의 원본 단가가 =NA()여도 앞의 기본 수식은 미등록을 표시합니다. 따라서 기본 네 행의 정상 단가를 벗어나면 아래 검사를 추가하세요.
6. 미등록·빈 단가·원본 오류를 분리하는 추가 검사
첨부 XLSX는 앞의 기본 수식 예제입니다. 아래 G:I 검사는 다운로드 파일에 미리 들어 있지 않으므로 파일 사본의 주문 시트에 직접 추가합니다. G1=키 개수, H1=조회 위치, I1=검사 단가를 적고 각 수식을 2행에 입력한 뒤 5행까지 복사하세요. C열의 기본 수식은 남겨 두어 두 결과를 비교합니다.
G2는 원본 키 개수입니다.
=COUNTIF(마스터!$A$2:$A$5,A2)H2는 키가 하나일 때만 위치를 계산합니다. P003의 위치는 범위 안 세 번째인 3이며, 워크시트 행 번호 4와 구분합니다.
=IF(G2=0,"미등록",IF(G2>1,"중복 확인",MATCH(A2,마스터!$A$2:$A$5,0)))I2는 원본 셀의 실제 빈칸, 논리값, 숫자 여부를 차례로 확인합니다.
=IF(G2<>1,H2,IF(ISBLANK(INDEX(마스터!$C$2:$C$5,H2)),"단가 비어 있음",IF(ISLOGICAL(INDEX(마스터!$C$2:$C$5,H2)),"단가 형식/오류 확인",IF(ISNUMBER(INDEX(마스터!$C$2:$C$5,H2)),INDEX(마스터!$C$2:$C$5,H2),"단가 형식/오류 확인"))))기본 입력에서 I2:I5는 89000, 1200000, 45000, 미등록입니다. 검사 결과로 금액을 따로 계산하려면 J1=검사 금액, J2에 =IF(ISNUMBER(I2),B2*I2,"확인 필요")를 입력하고 J5까지 복사합니다. 기본 수량 2·1·3·1을 전제로 정상 세 건의 합계는 여전히 1513000이며 P999는 확인 필요로 남습니다. I열만 추가하고 기존 F열 합계를 그대로 사용하면 원본 오류에 대한 금액 계산까지 보호된 것은 아닙니다. 수량의 빈칸·음수·소수 허용 여부는 별도 입력 규칙으로 정해야 합니다.
| 사본에서 한 번에 하나만 바꿀 값 | 기본 C열에서 생기는 문제 | 검사 I열의 기대값 |
|---|---|---|
| 마스터!C4에 =NA() | 등록된 P003인데 미등록으로 표시 | 단가 형식/오류 확인 |
| 마스터!C4의 내용을 지워 실제 빈 셀로 만들기 | 빈 단가가 0으로 보임 | 단가 비어 있음 |
| 마스터!C4에 숫자 0 | 빈 단가 때와 같은 숫자 0 | 숫자 0 유지 |
마스터!C4에 텍스트 89000, ="", TRUE 또는 =1/0 |
숫자가 아닌 값이나 원본 오류가 전달됨 | 단가 형식/오류 확인 |
| 마스터!A5를 P003으로 변경 | 첫 P003만 찾아 중복을 드러내지 못함 | 중복 확인 |
| 주문!A2를 P999로 변경 | 이번에는 실제로 없는 키 | 미등록 |
각 시험 뒤 C4=숫자 89000, A5=P004, 주문!A2=P003으로 복원하고 I2=89000, G2=1, H2=3, J2=178000을 다시 대조합니다. 날짜 일련번호나 음수도 숫자일 수 있으므로 이 검사는 정상 판매가격의 업무 규칙까지 보장하지 않습니다. 키는 이 글의 P001~P999처럼 와일드카드 문자가 없는 코드이고, 대소문자를 구분하지 않는다는 전제입니다. 임의의 *·?·~가 들어 있는 키에 그대로 확대하지 않습니다.
추가 수식의 정상·미등록·중복·0·실제 빈칸·#N/A·숫자 문자열·빈 문자열·논리값·나눗셈 오류 10개 입력은 수식 캐시를 비운 별도 시험 파일을 LibreOffice에서 재계산해 기대값과 대조했습니다. Microsoft Excel 앱에서 실행한 결과는 아니므로 Excel 사본에서도 같은 시험을 마친 뒤 적용하세요. 함수 의미는 Microsoft의 IFNA, IS 함수, INDEX 설명을 기준으로 확인했습니다.
첨부 파일의 사용 범위
첨부 예제는 마스터와 주문 시트, 숫자형 단가, 키별 조회·대조·금액 계산을 포함합니다. 계산 기대값은 위 가상 입력에서 산출한 값입니다. 제작 과정에서 Microsoft Excel 앱을 실행한 기록은 없으므로 파일을 연 뒤 수식 결과와 불일치 시험을 확인하세요. XLOOKUP 제공 여부와 무관하게 이 글의 검산은 VLOOKUP으로 수행합니다.
공식 문서
-
VLOOKUP 함수: 첫 열 조회, 정확·근사 일치, 열 번호 오류.
출처 원문 수집일: S1 2026-09-23. 수식의 결과와 원본 제품별 단가를 함께 확인한 뒤 업무 파일에 적용합니다.
작성자 소개
