엑셀 VLOOKUP으로 상품코드 A2의 단가를 찾는 기본식은 =VLOOKUP(A2,$F$2:$H$100,3,FALSE)입니다. A2가 조회값, F2:H100이 기준표, 3이 기준표 왼쪽부터 반환할 열 번호, FALSE가 정확히 일치 조건입니다. 사번·상품코드·거래처코드처럼 정확한 키를 찾을 때는 마지막 인수를 생략하지 않습니다.
VLOOKUP은 지정한 범위의 첫 번째 열을 위에서 아래로 검색하고 같은 행의 오른쪽 열 값을 가져옵니다. 조회열보다 왼쪽에 있는 값을 반환할 수 없고 열을 삽입하면 고정 열번호가 달라질 수 있습니다. 최신 Excel에서는 방향 제한이 없고 기본값이 정확히 일치인 XLOOKUP도 고려합니다.
VLOOKUP 구문 네 가지
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) 순서입니다. 네 인수를 한국어로 바꾸면 ‘무엇을, 어디 첫 열에서, 몇 번째 열 값을, 어떤 일치 방식으로’ 찾을지 지정하는 함수입니다.
| 인수 | 역할 | 예시 |
|---|---|---|
| lookup_value | 찾을 값 | A2 상품코드 |
| table_array | 조회표 전체 | $F$2:$H$100 |
| col_index_num | 반환 열 번호 | 3 |
| range_lookup | 일치 방식 | FALSE |
상품 단가 조회 예제
원본 구조
주문표 A열에 상품코드가 있고 기준표 F열 상품코드, G열 상품명, H열 단가가 있다고 가정합니다. 조회값인 상품코드는 기준표 범위 F:H의 첫 열 F에 있어야 합니다. 단가는 기준표의 세 번째 열입니다.
수식 입력
주문표 단가 셀에 =VLOOKUP(A2,$F$2:$H$100,3,FALSE)를 입력합니다. Excel 언어·지역설정에 따라 인수 구분자가 쉼표 대신 세미콜론일 수 있습니다. 자동완성 설명을 보며 괄호와 인수 순서를 확인합니다.
아래로 복사
수식 셀 오른쪽 아래 채우기 핸들을 더블클릭하거나 드래그합니다. A2는 A3, A4로 바뀌지만 기준표 `$F$2:$H$100`은 절대참조라 고정됩니다. F4 키로 상대·절대참조 상태를 전환할 수 있습니다.
검산
기준표의 첫 코드, 중간 코드, 마지막 코드와 존재하지 않는 코드를 각각 시험합니다. 결과가 맞아 보여도 코드 중복이 있으면 VLOOKUP은 첫 번째 일치값만 반환합니다. 기준표에서 코드가 고유한지 중복 제거 전에 확인합니다.
FALSE와 TRUE 차이
FALSE: 정확히 일치
FALSE 또는 0은 첫 열에서 정확히 같은 값을 찾습니다. 일치값이 없으면 #N/A를 반환합니다. 상품코드, 주민번호가 아닌 내부 식별자, 사번, 계정코드처럼 하나의 정답이 필요한 업무에는 보통 FALSE를 사용합니다.
TRUE: 근사 일치
TRUE 또는 1은 정확한 값이 없으면 조회값보다 작거나 같은 가장 가까운 구간을 찾습니다. 첫 열은 오름차순으로 정렬되어 있어야 합니다. 성적 등급표, 수수료 구간, 할인율 구간처럼 하한값 기준표에서 사용합니다.
마지막 인수 생략 위험
range_lookup을 생략하면 기본값은 TRUE 근사 일치입니다. 기준표가 정렬되지 않았는데 생략하면 #N/A가 아니라 그럴듯한 오답을 반환할 수 있어 더 위험합니다. 정확 조회라면 항상 FALSE를 명시합니다.
열 번호 계산법
열번호는 워크시트의 실제 열 문자와 무관하고 table_array의 왼쪽 끝을 1로 셉니다. 범위가 F:H라면 F=1, G=2, H=3입니다. H열이라고 8을 넣으면 범위가 세 열뿐이라 #REF! 오류가 납니다.
기준표 중간에 열을 삽입하면 `3`이 더 이상 단가열을 가리키지 않을 수 있습니다. 작은 고정표는 수식을 수정하고, 변동이 많은 표는 MATCH로 열번호를 찾거나 XLOOKUP, INDEX/MATCH, 구조화된 참조를 사용합니다.
절대참조가 필요한 이유
수식을 아래로 복사할 때 기준표가 `F2:H100`이면 다음 행에서 `F3:H101`로 밀립니다. 첫 상품코드가 범위에서 빠지고 마지막에 빈 행이 추가되어 일부 결과가 #N/A로 바뀝니다. `$F$2:$H$100`으로 고정하면 모든 행이 같은 기준표를 봅니다.
조회값 A2는 행마다 바뀌어야 하므로 상대참조로 둡니다. 기준표를 Excel 표로 만들고 `상품표`처럼 이름을 지정하면 범위 확장과 가독성을 개선할 수 있습니다. 새 상품을 추가했을 때 조회범위에 자동 포함되는지 테스트합니다.
#N/A 오류 해결
조회값이 실제로 없음
기준표에 코드가 없으면 정상적으로 #N/A가 나옵니다. 오류를 숨기기 전에 누락 상품인지 입력 오류인지 확인합니다. `=IFERROR(VLOOKUP(…),"미등록")`처럼 업무 의미가 드러나는 문구를 쓸 수 있습니다.
숫자와 텍스트 형식 불일치
화면에 모두 1001로 보여도 한쪽은 숫자, 다른 쪽은 텍스트면 일치하지 않습니다. 오류표시에서 숫자로 변환하거나 VALUE·TEXT 함수를 사용해 두 데이터의 형식을 통일합니다. 서식만 일반으로 바꿔서는 값 형식이 변하지 않을 수 있습니다.
앞뒤 공백과 보이지 않는 문자
외부 시스템에서 붙여넣은 코드에는 앞뒤 공백, 줄바꿈, 인쇄 불가능 문자가 섞일 수 있습니다. LEN으로 길이를 비교하고 TRIM·CLEAN으로 정리합니다. 하이픈, 전각문자, 서로 다른 따옴표도 육안으로 같아 보일 수 있습니다.
범위 첫 열 오류
찾을 값이 table_array의 첫 열에 없으면 VLOOKUP이 검색하지 못합니다. 상품코드가 G열인데 범위를 F:H로 잡으면 F열을 검색합니다. 범위를 G:I로 바꾸거나 XLOOKUP을 사용합니다.
#REF·#VALUE·#NAME 오류
#REF!
반환 열번호가 조회범위 열 수보다 크거나 참조 열을 삭제했을 때 발생합니다. 범위의 열 개수를 다시 세고 반환열이 포함됐는지 확인합니다. 열 삽입·삭제가 잦다면 고정 숫자 대신 다른 조회방식을 검토합니다.
#VALUE!
열번호가 1보다 작거나 인수 형식이 잘못된 경우 발생할 수 있습니다. Microsoft는 조회값이 255자를 초과하는 경우 INDEX/MATCH 같은 대안을 안내합니다. 수식 평가 기능으로 어느 인수에서 오류가 생기는지 확인합니다.
#NAME?
함수 이름 철자, 텍스트 따옴표, 이름 정의 오류를 확인합니다. 직접 문자열을 찾을 때는 "서울"처럼 큰따옴표로 묶습니다. 함수명을 한글로 번역해 입력하지 않습니다.
IFERROR 사용 시 주의
=IFERROR(VLOOKUP(A2,$F$2:$H$100,3,FALSE),"미등록")은 사용자 화면을 깔끔하게 만듭니다. 그러나 #N/A뿐 아니라 #REF, #VALUE 등 수식 설계 오류까지 모두 숨깁니다. 개발·검수 단계에서는 원래 오류를 확인한 뒤, 운영용에서만 의미 있는 대체문구를 적용합니다.
금액 결과를 0으로 바꾸면 실제 단가 0과 미등록 상품을 구분할 수 없습니다. 빈 문자열도 누락을 감춥니다. 별도 상태열에 등록 여부를 표시하거나 미등록 건수를 COUNTIF로 감시합니다.
XLOOKUP으로 바꾸는 기준
Microsoft는 XLOOKUP을 VLOOKUP의 개선된 버전으로 안내합니다. 조회열과 반환열을 따로 지정하므로 왼쪽 값도 반환할 수 있고 기본값이 정확히 일치입니다. 같은 예제는 =XLOOKUP(A2,F2:F100,H2:H100,"미등록")처럼 작성합니다.
다만 XLOOKUP은 Excel 2016과 2019에서 사용할 수 없습니다. 파일을 구버전 사용자와 공유한다면 VLOOKUP 또는 INDEX/MATCH 호환성을 유지해야 합니다. 최종 사용자 버전과 저장형식을 먼저 확인합니다.
실무 검증 체크리스트
- 조회값이 기준범위 첫 열에 있습니다.
- 정확 조회는 마지막 인수를 FALSE로 명시했습니다.
- 기준범위를 절대참조 또는 표 이름으로 고정했습니다.
- 열번호를 범위 왼쪽부터 1로 계산했습니다.
- 조회코드의 숫자·텍스트 형식을 통일했습니다.
- 앞뒤 공백과 숨은 문자를 점검했습니다.
- 기준키 중복과 미등록 건을 별도로 확인했습니다.
- 첫·중간·마지막·없는 코드로 테스트했습니다.
자주 묻는 질문
Q1. VLOOKUP 마지막에 FALSE를 왜 넣나요?
정확히 같은 값만 찾도록 지정합니다. 생략하면 기본이 TRUE 근사조회라 정렬되지 않은 표에서 잘못된 값을 반환할 수 있습니다.
Q2. 조회값이 범위 가운데 열에 있어도 되나요?
안 됩니다. VLOOKUP은 table_array의 첫 번째 열에서만 찾습니다. 범위를 다시 잡거나 XLOOKUP·INDEX/MATCH를 사용하세요.
Q3. 아래로 복사하면 일부만 #N/A가 됩니다.
기준범위가 상대참조라 밀렸는지 확인합니다. `$F$2:$H$100`처럼 절대참조로 고정하세요.
Q4. 코드가 있는데 #N/A가 뜹니다.
숫자와 텍스트 형식, 앞뒤 공백, 숨은 문자, 하이픈 차이를 확인합니다. LEN과 TRIM·CLEAN으로 점검하세요.
Q5. VLOOKUP과 XLOOKUP 중 무엇을 쓰나요?
최신 Excel만 사용하면 XLOOKUP이 유연합니다. Excel 2016·2019 사용자와 공유하면 VLOOKUP 호환성을 고려하세요.
공식 출처
- Microsoft 지원 – VLOOKUP 함수
- Microsoft 지원 – #N/A 오류 수정
- Microsoft 지원 – XLOOKUP 함수
- Microsoft 지원 – VLOOKUP #VALUE 오류 수정
Microsoft 365용 Excel과 Excel 2024 공개도움을 기준으로 작성했습니다. 조직의 구버전 호환성과 지역별 인수 구분자 설정을 확인하세요.