VLOOKUP 사용법: 기본 수식부터 일치 방식과 오류 해결까지

VLOOKUP을 쓰기 전에 확인할 조건

상품 코드로 단가를 찾거나 사번으로 직원 이름을 가져올 때 VLOOKUP을 사용할 수 있어요. 기준표와 입력표를 연결하면, 행마다 표를 찾아다니며 값을 옮기는 과정을 수식으로 바꿀 수 있어요.

핵심은 표의 배치예요. 검색 기준은 지정한 범위의 맨 왼쪽 열에 있어야 하고, 가져올 값은 같은 범위 안에 있어야 해요. 기준표의 위치를 먼저 확인하면 수식을 여러 번 고치는 일을 줄일 수 있어요.

표의 기능과 제약은 마이크로소프트의 인수 설명과 권장 사용법을 기준으로 정리했어요.

VLOOKUP 기본 수식과 인수별 설정 방법

수식은 검색값, 검색 범위, 반환 열 번호, 일치 방식 순서로 구성해요. 복잡해 보이나요? 원리는 간단해요.

=VLOOKUP(검색값,검색범위,반환열번호,일치방식)

앞의 항목들은 무엇을 어디서 찾아 무엇을 가져올지 정해요. 마지막 일치 방식은 생략할 수 있지만, 생략하면 근사 일치가 적용되므로 처음에는 항상 적는 편이 좋아요.

인수의 의미와 기본 동작은 공식 구문 설명에서 확인할 수 있어요. 셀 주소와 열 번호는 아래 연습용 기준표에 맞춘 작성 예시예요.

특히 반환 열 번호를 시트의 열 번호와 혼동하지 마세요. 검색 범위를 B2:D11로 지정했다면 범위 안에서 B열이 첫 번째, C열이 두 번째, D열이 세 번째예요.

따라서 D열의 값을 가져올 때는 3을 사용해요. 범위의 시작 열이 바뀌면 반환 열 번호도 다시 확인해야 한다는 점이 중요해요.

기준표를 만들고 수식 복사까지 따라 해요

다음은 상품 코드로 단가를 가져오는 가상의 연습 자료예요. 상품명과 금액은 사용법을 설명하기 위한 임의 값이며, 실제 판매 가격이나 시장 통계가 아니에요.

새 시트의 A1 셀을 선택하고 아래 내용을 붙여 넣어 보세요. 코드, 상품명, 단가가 각각 다른 열에 들어가도록 탭으로 구분한 자료예요.

상품코드	상품명	단가
P-A	노트	3000
P-B	파일	2000
P-C	펜	1000

1. 1
   기준표 배치 A열에 상품 코드, B열에 상품명, C열에 단가가 들어갔는지 확인해요.
2. 2
   검색값 입력 E2 셀에 찾을 상품 코드인 P-B를 입력해요.
3. 3
   수식 작성 F2 셀에 =VLOOKUP(E2,$A$2:$C$4,3,FALSE)를 입력해요.
4. 4
   결과 확인 F2에 연습 자료의 파일 단가인 2000이 표시되는지 확인해요.
5. 5
   아래로 복사 E열에 다른 코드를 입력하고 F2의 수식을 아래로 복사해요.

이 수식은 E2의 코드를 기준표의 A열에서 찾아요. 일치하는 행을 찾으면 선택한 범위에서 세 번째 열인 C열의 값을 반환해요.

같은 코드로 상품명을 가져오고 싶다면 반환 열 번호만 바꾸면 돼요. 검색 기준과 범위는 유지하고, 가져올 정보의 위치를 바꾸는 방식이에요.

=VLOOKUP(E2,$A$2:$C$4,2,FALSE)

검색 범위는 고정하고 검색값은 움직이게 해요

$A$2:$C$4의 달러 기호는 기준표의 열과 행을 고정해요. 반면 E2는 고정하지 않아, 수식을 아래로 복사하면 다음 행의 검색값을 참조하게 돼요.

F2에 입력한 수식
=VLOOKUP(E2,$A$2:$C$4,3,FALSE)

F3으로 복사한 수식
=VLOOKUP(E3,$A$2:$C$4,3,FALSE)

기준표는 그대로 두고 조회 대상만 바뀌게 만드는 것이 범위 고정의 목적이에요. 복사 후 결과가 이상하다면 검색값이 바뀌었는지와 기준표가 유지됐는지를 따로 확인해 보세요.

이 예시는 공식 도움말이 안내하는 절대 참조를 이용한 채우기 방식을 적용했어요. 고정한 범위 밖에 새 자료를 추가했다면, 그 자료가 검색 범위에 포함됐는지도 별도로 확인해야 해요.

FALSE와 TRUE는 언제 선택하나요?

특정 상품 코드나 사번을 찾는다면 FALSE를 선택해요. 정확한 값이 없을 때 다른 항목의 정보를 가져오지 않고, 일치하는 값이 없다는 오류를 확인할 수 있어요.

TRUE는 정렬된 기준표에서 근사 값을 찾을 때 사용해요. 첫 번째 열이 정렬되어 있다는 전제가 있으므로, 정렬 여부를 확인하지 않은 채 쓰면 기대와 다른 값이 나올 수 있어요.

비교 기준은 공식 도움말의 일치 방식과 일반적인 문제 설명이에요. 근사 일치를 위해 정렬할 때는 검색 기준과 반환값의 행 관계가 유지되도록 기준표를 함께 다뤄야 해요.

근사 일치는 오류를 없애는 설정이 아니에요

정확히 일치에서 #N/A가 나온다고 마지막 인수를 TRUE로 바꾸면 검색의 의미가 달라져요. 오류 표시가 사라지더라도 원래 찾으려던 상품이나 직원의 정보인지 보장할 수 없어요.

예를 들어 앞의 연습표에 없는 P-Z를 조회한다면, 먼저 기준표에 코드가 등록됐는지 확인해야 해요. 조회 실패를 확인하는 과정도 데이터 검증의 일부로 생각하면 잘못된 결과를 업무에 사용하는 일을 줄일 수 있어요.

#N/A·#REF! 오류는 이 순서로 점검해요

#N/A와 #REF!는 확인할 지점이 달라요. 전자는 검색값과 일치 방식부터, 후자는 검색 범위와 반환 열 번호부터 살펴보면 원인을 좁히기 쉬워요.

오류를 보기 좋게 감추기 전에 원본 수식과 기준표를 확인하세요. 특히 정확히 일치 검색에서 값이 없다는 표시는 누락된 기준 데이터를 발견하는 단서가 될 수 있어요.

점검표는 마이크로소프트의 오류 설명과 데이터 정리 권장 사항에 근거해요. 숫자나 날짜가 텍스트로 저장되어 있으면 잘못되거나 예상하지 못한 결과가 나올 수 있다고 안내해요.

#REF!는 범위 안의 열 개수와 비교해요

아래는 연습표의 검색 범위가 A열부터 C열까지인데, 그 범위에 없는 열을 요청한 예시예요. 반환 열 번호는 반드시 선택한 범위 안에서 유효해야 해요.

범위를 벗어난 반환 열 번호
=VLOOKUP(E2,$A$2:$C$4,4,FALSE)

단가 열을 가져오도록 수정
=VLOOKUP(E2,$A$2:$C$4,3,FALSE)

반환하려는 값이 실제로 더 오른쪽에 있다면 범위 자체를 넓히는 방법도 있어요. 번호만 바꾸기 전에 어떤 정보를 가져오려는지부터 확인하면 오류를 고치면서 다른 열의 값을 반환하는 실수를 피하기 쉬워요.

공백과 저장 형태도 살펴보세요

복사해서 가져온 텍스트에는 앞뒤 공백이나 눈에 보이지 않는 문자가 섞일 수 있어요. 공식 도움말은 이런 경우 데이터를 점검하고 TRIM이나 CLEAN 함수를 이용한 정리를 권장해요.

모든 조회 실패를 공백 문제로 단정할 필요는 없어요. 검색값 존재 여부, 범위의 시작 열, 일치 방식을 먼저 확인한 다음 문자와 저장 형태를 살펴보면 점검 순서가 명확해져요.

대안 함수와 유료 도구는 어떻게 판단하나요?

검색 기준보다 왼쪽에 있는 값을 가져와야 한다면 VLOOKUP의 배치 제약이 불편할 수 있어요. 마이크로소프트는 어느 방향으로든 검색할 수 있고 정확히 일치가 기본인 XLOOKUP을 대안으로 안내해요.

아래 비교는 직접 측정한 성능 평가가 아니라 공식 도움말에 명시된 기능 비교예요. 속도나 작업 시간 절감률은 확인된 자료가 없으므로 비교하지 않았어요.

출처는 마이크로소프트 공식 도움말의 대안 함수 안내예요. 현재 사용하는 환경의 지원 여부는 실제 적용 전에 확인해 주세요.

무료·유료 비교에서 지금 판단할 수 있는 범위

제공된 공식 자료에는 무료 이용 범위, 구독 가격, 무료체험 조건이 없어요. 따라서 특정 요금제의 가격이나 무료 사용 가능 여부를 단정하지 않아요.

공식 도움말은 코파일럿 구독자가 함수 삽입과 사용에 도움을 받을 수 있다고 안내해요. 다만 이 글의 조회 작업을 구성하는 데 필요한 핵심은 검색 기준과 범위 설정이므로, 추가 결제를 검토하기 전에 현재 엑셀 환경에서 연습 수식을 먼저 적용해 보는 편이 합리적이에요.

업무에 적용하기 전 최종 점검

VLOOKUP의 실무 가치는 긴 수식을 작성하는 데 있지 않아요. 어떤 값을 기준으로 조회하고, 실패했을 때 무엇을 확인할지 팀 안에서 일관되게 정하는 데 있어요.

상품 관리라면 코드와 상품 정보의 연결을, 직원 명부라면 사번과 이름의 연결을 먼저 정리해 보세요. 기준표가 명확해야 수식의 결과도 검토하기 쉬워져요.

• 기준 열 검색값이 선택한 범위의 첫 번째 열에 있는지 확인해요.
• 반환 열 가져올 값이 범위에 포함되고 열 번호가 맞는지 확인해요.
• 일치 방식 정확한 식별값 조회에는 FALSE를 명시했는지 확인해요.
• 범위 고정 복사한 수식에서도 기준표의 주소가 유지되는지 확인해요.
• 결과 검토 반환값과 오류가 나온 행을 기준표와 대조해요.

처음에는 이 글의 연습 자료로 동작을 확인하고, 같은 구조를 실제 업무표에 적용해 보세요. 검색값 → 기준표 → 반환 열 → 일치 방식의 순서로 읽으면 다른 사람이 만든 VLOOKUP 수식도 이해하기 쉬워져요.

FAQ