사원번호 목록 하나, 급여 목록 하나. 두 표를 눈으로 대조하며 복사·붙여넣기 하다가 줄이 밀려본 적 있으시죠. VLOOKUP은 바로 그 작업을 한 줄로 끝내는 함수입니다. 인수 네 개의 의미만 정확히 알면 5분이면 익히고, 대부분의 오류는 네 번째 인수와 범위 고정 두 가지에서 나옵니다.
VLOOKUP이 요구하는 인수 네 개
기본 형태는 이렇습니다.
=VLOOKUP(찾을값, 범위, 열번호, 일치옵션)
- 찾을값: 기준이 되는 값. 사원번호, 상품코드처럼 중복 없는 값이 좋습니다.
- 범위: 찾아볼 표 전체. 중요한 규칙 하나 — 찾을값은 반드시 이 범위의 첫 번째 열에 있어야 합니다.
- 열번호: 범위의 왼쪽 끝을 1로 셌을 때, 가져올 값이 몇 번째 열인지. 시트 전체의 열 번호가 아니라 지정한 범위 안에서 세는 숫자입니다. 여기서 실수가 제일 많습니다.
- 일치옵션: 0(FALSE)은 정확히 일치, 1(TRUE)은 근사값. 생략하면 TRUE로 처리됩니다.
상품코드로 단가 가져오기 예시
A열에 상품코드, B열에 상품명, C열에 단가가 A2:C200에 있다고 해봅시다. F2에 찾을 상품코드를 넣고 단가를 가져오려면
=VLOOKUP(F2, $A$2:$C$200, 3, 0)
단가는 범위의 세 번째 열이니 열번호는 3입니다. 상품명을 원하면 2로 바꾸면 끝입니다.
범위에 달러 기호를 붙인 이유는 아래로 채우기 할 때 범위가 같이 밀려 내려가는 걸 막기 위해서입니다. 수식 입력 중 범위를 드래그한 뒤 F4 키를 누르면 자동으로 붙습니다. 이 한 가지만 습관 들여도 "위쪽 몇 줄은 맞는데 아래쪽부터 #N/A" 현상이 사라집니다.
0과 1, 무엇을 언제 쓰나
| 옵션 | 동작 | 조건 | 쓰는 상황 |
|---|---|---|---|
| 0 (FALSE) | 완전히 같은 값만 찾음 | 정렬 불필요 | 코드·이름 조회 등 거의 모든 경우 |
| 1 (TRUE) | 찾을값 이하의 가장 큰 값 | 첫 열이 오름차순 정렬 | 점수 구간별 등급, 금액 구간별 요율 |
솔직히 말하면 실무 수식의 9할은 0입니다. 1은 90점 이상 A, 80점 이상 B처럼 구간표를 만들 때만 쓰세요. 옵션을 아예 빼먹으면 TRUE가 적용돼 엉뚱한 값이 조용히 들어오는데, 오류 표시가 없어서 더 위험합니다.
오류가 떴을 때 점검 순서
| 오류 | 주된 원인 | 해결 |
|---|---|---|
| #N/A | 값이 정말 없음 / 숫자와 텍스트 형식 불일치 / 앞뒤 공백 | TRIM 함수로 공백 제거, 셀 서식 통일 |
| #REF! | 열번호가 범위의 열 개수보다 큼 | 범위를 넓히거나 열번호를 줄임 |
| #VALUE! | 열번호에 0 이하 또는 문자 입력 | 1 이상의 숫자로 수정 |
| 값은 나오는데 틀림 | 옵션 생략으로 근사 일치 적용 | 마지막 인수에 0 추가 |
#N/A 중 가장 흔한 건 한쪽은 숫자 1001, 다른 쪽은 텍스트 "1001"인 경우입니다. 셀 왼쪽 위에 초록색 삼각형이 보이면 텍스트로 저장된 숫자라는 뜻이니, 해당 열을 선택해 오류 표시에서 숫자로 변환하면 바로 풀립니다.
왼쪽 값은 못 가져옵니다
VLOOKUP의 구조적 한계입니다. 찾을값의 왼쪽 열은 절대 가져올 수 없습니다. 방법은 세 가지. 열 순서를 바꾸거나, INDEX와 MATCH를 조합하거나, XLOOKUP을 쓰는 겁니다.
=XLOOKUP(F2, A2:A200, C2:C200, "없음")
XLOOKUP은 열 번호를 셀 필요가 없고 방향 제약도 없으며, 네 번째 인수로 못 찾았을 때 표시할 문구까지 지정합니다. 다만 Microsoft 365와 Excel 2021 이상에서만 동작합니다. 2019 이하 버전 사용자와 파일을 주고받는다면 상대 쪽에서 열리지 않으니, 공용 파일은 아직 VLOOKUP으로 두는 편이 안전합니다.
자주 묻는 질문(FAQ)
Q. VLOOKUP 결과에 #N/A 대신 빈칸이나 다른 문구를 표시할 수 있나요?
A. IFERROR로 감싸면 됩니다. =IFERROR(VLOOKUP(F2,$A$2:$C$200,3,0),"") 형태로 쓰면 못 찾았을 때 빈칸이 표시됩니다. 다만 실제 데이터 누락까지 가려버리니, 검수가 끝난 뒤에 씌우는 걸 권합니다.
Q. 찾을값이 여러 개 있으면 어떤 값을 가져오나요?
A. 위에서부터 처음 만나는 하나만 가져오고 나머지는 무시합니다. 중복 건을 모두 보려면 피벗 테이블이나 필터를 쓰는 편이 낫습니다.
Q. 다른 파일에 있는 표도 참조할 수 있나요?
A. 가능합니다. 두 파일을 모두 열어둔 상태에서 범위를 드래그하면 파일 경로가 자동으로 들어갑니다. 단 원본 파일을 닫거나 옮기면 경로가 깨질 수 있어, 중요한 자료는 같은 통합 문서 안의 다른 시트로 옮겨두는 쪽이 안정적입니다.
마무리
범위는 F4로 고정하고 마지막 인수에 0을 넣는 습관만 들이면, VLOOKUP에서 겪는 문제의 대부분은 애초에 생기지 않습니다.