엑셀 VLOOKUP 함수 쉽게 쓰는 법 조건과 오류까지 정리

VLOOKUP이 찾는 방식

VLOOKUP은 한 표에서 기준값을 찾고 같은 행의 다른 열 값을 가져오는 엑셀 함수입니다. 거래처 코드로 담당자 이름을 찾거나 상품 번호로 단가를 불러올 때 특히 유용합니다. 처음에는 수식이 길어 보이지만 찾을 값,참조할 표,가져올 열,일치 방식이라는 네 요소로 나누면 이해가 쉬워집니다. 작업 전에 어떤 값을 기준으로 연결할지 먼저 정하면 표를 훨씬 안정적으로 만들 수 있습니다.

함수 기본 모양은 VLOOKUP 찾을값 표범위 열번호 일치방식입니다. 찾을값은 현재 표에서 검색할 셀이고 표범위는 기준값과 결과값이 함께 있는 영역입니다. 열번호는 선택한 표범위의 첫 열을 1로 보아 몇 번째 열의 값을 가져올지 뜻합니다. 마지막 인수는 정확히 같은 값을 찾을지 비슷한 구간값을 찾을지 정하는 자리입니다. 이 네 항목을 각각 확인하면 복잡한 수식도 차분히 입력할 수 있습니다.

예를 들어 A열에 상품 코드가 있고 다른 시트의 A열부터 D열까지 코드,품명,규격,단가가 정리돼 있다고 가정해 보겠습니다. 현재 시트 B2에 품명을 표시하려면 VLOOKUP A2 기준표범위 2 FALSE처럼 입력합니다. 실제 엑셀에서는 범위와 인수를 쉼표로 구분합니다. 여기서 2는 기준표범위 안에서 두 번째 열인 품명을 뜻하며 FALSE는 정확한 코드만 찾으라는 조건입니다.

수식을 복사해 아래 행에도 적용할 때는 표범위를 절대참조로 고정하는 습관이 중요합니다. 기준표범위를 드래그한 뒤 F4 키를 눌러 달러 표시를 붙이면 아래로 채워도 범위가 이동하지 않습니다. 찾을값 A2는 행마다 바뀌어야 하므로 보통 상대참조로 둡니다. 이 차이를 놓치면 첫 행은 맞지만 다음 행부터 엉뚱한 결과가 나올 수 있어 복사 전에 셀 참조를 확인해야 합니다.

수식 입력 순서

대부분의 목록 조회에는 마지막 인수로 FALSE를 사용합니다. FALSE는 코드나 이름처럼 정확히 일치하는 값만 반환하므로 재고표,직원 명단,주문 목록에 적합합니다. TRUE는 구간별 수수료나 점수 등급처럼 가까운 값을 찾을 때 쓰지만 기준 열이 오름차순으로 정렬돼 있어야 합니다. 정렬 조건을 확인하지 못했다면 우선 FALSE로 시작하는 편이 안전하고 결과 검증도 쉽습니다.

VLOOKUP에서 가장 흔한 오류는 N A입니다. 찾을값이 기준표의 첫 번째 열에 없거나 숫자와 텍스트 형식이 서로 다를 때 발생합니다. 화면에는 같은 1001처럼 보여도 한쪽이 텍스트이면 일치하지 않을 수 있습니다. 셀 왼쪽 위 경고 표시,앞뒤 공백,숨은 문자 여부를 확인하고 필요하면 VALUE나 TRIM 함수로 형식을 맞춰 보세요. 앞자리 0이 필요한 코드는 두 표를 텍스트로 통일합니다.

표범위의 첫 열은 반드시 검색 기준값이어야 한다는 점도 기억해야 합니다. VLOOKUP은 표범위에서 왼쪽에서 오른쪽으로만 값을 가져옵니다. 품명을 기준으로 왼쪽에 있는 상품 코드를 가져오는 구조는 만들 수 없습니다. 이런 경우 열 순서를 바꾸거나 XLOOKUP 또는 INDEX와 MATCH 조합을 검토하면 됩니다. 함수 선택 전에 데이터가 어느 방향으로 배치됐는지 보는 일이 먼저입니다.

열번호 오류도 자주 발생합니다. 표범위를 A열부터 D열까지 지정했는데 열번호에 5를 쓰면 참조할 열이 없어 REF 오류가 납니다. 열번호는 시트 전체의 열 문자가 아니라 선택한 표범위 안에서 세는 번호입니다. 범위를 다시 잡은 뒤 첫 열부터 결과 열까지 손가락으로 세어 보면 실수를 줄일 수 있습니다. 중간 열을 삽입했다면 수식 결과도 다시 확인해야 합니다.

VLOOKUP 수식 입력 순서를 보여 주는 정보카드 — 찾을 값부터 열 번호와 일치 조건까지 순서대로 확인합니다.

정확히 찾기와 근사값

동일한 코드가 기준표에 여러 번 있다면 VLOOKUP은 가장 위에서 처음 만나는 값만 반환합니다. 따라서 기준표에는 중복 코드를 줄이고 최신 데이터가 위에 오도록 정리하는 기준이 필요합니다. 중복이 의도된 데이터라면 단일 값을 찾는 VLOOKUP보다 FILTER 같은 다른 기능이 더 알맞을 수 있습니다. 조회 결과가 이상할 때 수식만 보지 말고 원본 표의 중복 여부와 정렬 상태도 살펴보세요.

실무에서는 IFERROR와 함께 사용하면 보고서가 읽기 좋아집니다. 조회값이 없을 때 N A 대신 미등록 또는 확인 필요 같은 안내를 표시할 수 있기 때문입니다. 다만 모든 오류를 무조건 숨기면 범위나 열번호 실수를 발견하기 어렵습니다. 처음 수식을 만들 때는 오류 원인을 확인하고 검토가 끝난 뒤에만 IFERROR로 사용자용 메시지를 덧붙이는 순서가 좋습니다.

조회용 기준표는 표 머리글을 한 줄로 정하고 빈 행을 넣지 않는 편이 좋습니다. 병합 셀은 보기에는 편할 수 있지만 함수가 범위를 읽고 복사하는 과정에서는 혼란을 만듭니다. 코드 열에는 코드만,설명 열에는 설명만 넣어 한 셀에 여러 정보를 섞지 마세요. 이렇게 구조를 단순하게 유지하면 새 행을 추가한 뒤에도 참조 범위를 확인하기 쉽고 동료가 파일을 이어받아도 오류가 적습니다.

검증할 때는 정상 코드만 고르지 말고 없는 코드,중복 코드,앞자리 0이 있는 코드도 함께 시험합니다. 서로 다른 세 가지 이상 사례를 넣어 보면 정확 일치 조건과 오류 처리 문구가 의도대로 작동하는지 알 수 있습니다. 결과값을 원본 표와 나란히 두고 눈으로 비교하면 열번호를 하나 잘못 지정한 문제도 빨리 발견합니다. 마지막으로 보고서 목적에 맞춰 수식을 유지할지 값으로 고정할지 결정하세요.

오류를 줄이는 표 구성

먼저 기준표와 작업표에서 코드 열의 표시 형식을 확인합니다. 한쪽은 일반 서식이고 다른 쪽은 텍스트 서식이면 숫자가 같아 보여도 조회가 실패할 수 있습니다. 새 파일을 받을 때는 코드 열 전체를 한 번에 고치기보다 대표 값 몇 개를 골라 형식과 길이를 비교하세요. 원본 값을 바꿀 수 없다면 보조 열에서 형식을 변환하고,그 결과를 조회값으로 쓰는 방법도 있습니다.

범위를 지정할 때는 결과 열이 나중에 추가될 가능성도 생각합니다. 고정 범위만 사용하면 새 상품 행이 들어온 뒤 수식이 그 행을 찾지 못할 수 있습니다. 데이터가 자주 늘어나는 표라면 엑셀 표 기능으로 변환해 구조화된 참조를 쓰거나,관리자가 갱신할 때 확인할 범위를 문서화합니다. 반대로 너무 넓은 전체 열 참조는 큰 파일에서 계산 부담을 키울 수 있으므로 필요한 영역을 정하는 균형이 필요합니다.

수식 결과를 다른 사람에게 전달할 보고서라면 표시 문구도 업무 기준에 맞춥니다. 미등록이라고 표시할지 담당자 확인이라고 표시할지는 누가 다음 조치를 맡는지에 따라 달라집니다. IFERROR 안의 문구를 정하기 전에 오류가 생겼을 때 수정할 사람과 기준표를 관리할 사람을 정해 두면,단순히 오류를 숨기는 보고서가 되는 일을 막을 수 있습니다.

월별 자료처럼 같은 양식이 반복되는 경우에는 검증용 샘플을 따로 마련해 두는 것이 좋습니다. 정상 조회,없는 코드,텍스트 숫자,중복 코드 사례를 작은 점검표에 기록합니다. 새 기준표를 교체한 뒤 이 사례들을 입력하면 수식의 참조 범위와 일치 조건이 유지되는지 빠르게 확인할 수 있습니다. 이 과정은 수식을 처음 만든 사람과 파일을 인수받은 사람 모두에게 도움이 됩니다.

실무 점검표

마지막 점검에서는 수식 셀을 선택해 수식 입력줄의 찾을값,표범위,열번호,FALSE 여부를 차례로 읽습니다. 결과가 맞아 보여도 우연히 같은 값이 나온 경우가 있으므로 원본 표의 해당 행을 직접 대조합니다. 파일을 배포하기 전에는 필터나 숨김 행 때문에 누락된 데이터가 없는지도 확인합니다. 이런 짧은 점검을 반복하면 VLOOKUP을 단순한 기능이 아니라 신뢰할 수 있는 조회 절차로 활용할 수 있습니다.

여러 시트에서 같은 기준표를 쓴다면 기준표의 위치와 갱신 날짜를 눈에 띄게 표시합니다. 담당자가 임시 복사본을 수정한 뒤 다른 파일에서는 이전 표를 계속 참조하는 상황을 줄일 수 있습니다. 파일 이름만 믿기보다 기준표 상단에 적용일과 책임 부서를 기록하고,조회 수식을 가진 시트에서 참조 대상이 맞는지 정기적으로 확인합니다.

외부에서 받은 코드 목록은 붙여넣기 직후 데이터 정리 기능으로 공백과 형식을 점검하는 편이 좋습니다. 특히 웹페이지나 시스템 화면에서 복사한 값에는 보이지 않는 공백이 섞일 수 있습니다. 대표 코드 한 건만 수정해 결과가 맞는다고 판단하지 말고,목록의 앞과 중간과 끝에서 샘플을 골라 비교해야 누락이나 형식 차이를 찾기 쉽습니다.

작업을 마친 뒤에는 수식이 들어간 열과 값으로 확정한 열을 구분해 둡니다. 다음 달 보고서를 만드는 사람이 수식 결과를 덮어쓰거나 오래된 값을 다시 복사하는 실수를 막기 위해서입니다. 보호가 필요한 기준표는 편집 권한을 제한하고,변경 이력이 필요한 업무라면 수정일과 변경 이유를 별도 열에 남겨 두면 조회 결과를 추적하기 편합니다.