엑셀 VLOOKUP 사용법에서 막히는 지점은 거의 정해져 있습니다. 함수 구조가 어려운 것이 아니라, 지켜야 하는 제약 조건 세 가지를 모른 채 쓰다가 오류를 만나는 경우가 대부분입니다.
먼저 지금 보이는 오류가 무엇인지부터 확인하세요. 오류 종류에 따라 봐야 할 곳이 완전히 다릅니다.
오류로 원인 찾기
| 화면에 뜨는 것 | 원인 | 해결 |
|---|---|---|
| 첫 줄만 맞고 아래는 #N/A | 범위가 밀림 | F4로 절대참조 |
| 전부 #N/A | 값 불일치 또는 첫 열 규칙 위반 | 공백·형식·범위 확인 |
| #REF! | 열 번호가 범위의 열 개수보다 큼 | 열 번호를 다시 셈 |
| #VALUE! | 열 번호가 0 이하이거나 숫자가 아님 | 세 번째 인수 확인 |
| #NAME? | 함수 이름 오타 | 철자 확인 |
| 엉뚱한 값이 나옴 | 네 번째 인수를 비웠음 | 0 또는 FALSE 입력 |
엑셀 VLOOKUP 사용법 — 인수 네 개
기본 구조는 다음과 같습니다.
=VLOOKUP(찾을값, 범위, 열번호, 0)
- 찾을값 — 조회 기준이 되는 값. 사번, 상품코드처럼 중복되지 않는 항목이 적합합니다.
- 범위 — 데이터가 들어 있는 표 전체.
- 열번호 — 범위 안에서 가져올 열의 순번.
- 네 번째 인수 — 0(또는 FALSE)은 정확히 일치, 1(또는 TRUE)은 근사값.
제약 1 — 찾을값은 범위의 첫 열에 있어야 한다
VLOOKUP은 지정한 범위의 맨 왼쪽 열에서만 값을 찾습니다. 표에서 사번이 왼쪽, 이름이 오른쪽이라면 이름으로 사번을 찾는 것은 불가능합니다.
범위를 다시 지정해 기준 열이 맨 왼쪽에 오게 하거나, 아래에서 설명할 INDEX·MATCH 또는 XLOOKUP을 사용해야 합니다.
제약 2 — 열 번호는 범위 기준으로 센다
열 번호는 시트 전체가 아니라 지정한 범위 안에서 셉니다. 범위를 E열부터 잡았다면 E가 1, F가 2, G가 3입니다.
시트에서 C열이라는 이유로 3을 입력하면 전혀 다른 값이 반환됩니다. 값이 나오긴 하는데 틀린 값이라 발견이 늦어지는 유형입니다.
제약 3 — 네 번째 인수를 비우지 않는다
네 번째 인수를 생략하면 근사값 검색(TRUE)으로 동작합니다. 이 모드는 첫 열이 오름차순으로 정렬되어 있다고 가정하고 가장 가까운 값을 찾기 때문에, 정렬되지 않은 표에서는 엉뚱한 결과가 나옵니다.
구간별 등급 산정처럼 의도한 경우가 아니라면 항상 0을 입력하세요.
절대참조 — 드래그하면 #N/A가 뜨는 이유
첫 행은 정상인데 아래로 복사하면 #N/A가 이어지는 것은 가장 흔한 증상입니다.
수식을 아래로 복사하면 참조도 함께 이동합니다. E2:G50으로 지정한 범위가 다음 행에서 E3:G51, 그다음 행에서 E4:G52로 밀리면서 찾으려던 데이터가 범위 밖으로 빠져나갑니다.
해결은 범위를 절대참조로 고정하는 것입니다. $E$2:$G$50 형태로 바꾸면 복사해도 범위가 움직이지 않습니다.
직접 입력할 필요는 없습니다. 수식 입력줄에서 범위 부분을 드래그해 선택한 뒤 F4를 누르면 됩니다. F4를 반복해서 누르면 $E$2 → E$2 → $E2 → E2 순으로 순환하므로, 달러 기호가 양쪽에 모두 붙은 상태에서 멈춥니다.
노트북에서 F4가 밝기·음량 키로 동작한다면 Fn + F4를 사용하세요.
#N/A가 계속될 때 확인할 세 가지
보이지 않는 공백
외부 시스템에서 내려받은 데이터에는 값 뒤에 공백이 붙어 있는 경우가 많습니다. 엑셀은 "홍길동 "과 "홍길동"을 다른 값으로 취급합니다.
원본을 정리하려면 TRIM 함수를 사용하고, 수식만 고치려면 찾을값 자리에 TRIM(A2)를 넣습니다.
숫자로 보이지만 텍스트인 값
셀을 클릭했을 때 값이 왼쪽에 정렬되어 있으면 텍스트, 오른쪽이면 숫자입니다. 한쪽이 텍스트 1001이고 다른 쪽이 숫자 1001이면 일치하지 않습니다.
셀 왼쪽 위에 초록색 삼각형이 표시되어 있다면, 셀을 선택하고 나타나는 경고 아이콘에서 숫자로 변환을 선택합니다. 여러 셀을 한 번에 처리하려면 해당 열을 선택한 뒤 데이터 > 텍스트 나누기 > 마침을 실행하는 방법도 있습니다.
병합된 셀
범위 안에 병합된 셀이 있으면 병합 영역의 첫 칸에만 값이 들어 있고 나머지는 빈칸입니다. 조회 대상 표에서는 병합을 해제하고 값을 채워 두는 편이 안전합니다.
다른 시트·다른 파일에서 가져오기
참조할 표가 다른 시트에 있다면 범위 앞에 시트 이름과 느낌표를 붙입니다.
=VLOOKUP(A2, Sheet2!$E$2:$G$50, 3, 0)
직접 타이핑하는 것보다, 범위를 지정할 차례에 해당 시트 탭을 클릭하고 마우스로 드래그하는 편이 오타가 없습니다. 시트 이름에 공백이 포함되면 '판매 내역'!$E$2:$G$50처럼 작은따옴표가 자동으로 붙습니다.
다른 엑셀 파일을 참조하는 것도 가능하지만, 원본 파일이 닫혀 있거나 경로가 바뀌면 수식이 깨집니다. 정기적으로 쓰는 자료라면 같은 파일 안에 시트로 두는 편이 안정적입니다.
조건이 두 개 이상일 때
VLOOKUP은 조회 조건을 하나만 받습니다. 지점과 상품명을 동시에 만족하는 값을 찾는 식의 다중 조건은 그대로는 처리되지 않습니다.
가장 간단한 방법은 보조 열을 만드는 것입니다. 빈 열에 =B2&C2처럼 두 값을 이어 붙인 열을 만들고, 그 열을 기준으로 조회합니다. 이때 보조 열이 범위의 맨 왼쪽에 오도록 배치해야 합니다.
찾을값도 같은 방식으로 이어 붙입니다. =VLOOKUP(F2&G2, $A$2:$D$100, 4, 0) 형태가 됩니다.
Microsoft 365나 Excel 2021 이상을 쓴다면 XLOOKUP으로 더 간단히 처리할 수 있습니다. 아래에서 이어서 설명합니다.
VLOOKUP 대신 쓰는 함수
| 함수 | 가능한 것 | 필요한 버전 |
|---|---|---|
| VLOOKUP | 기준이 맨 왼쪽에 있을 때만 | 모든 버전 |
| INDEX + MATCH | 기준이 어느 열에 있어도 가능 | 모든 버전 |
| XLOOKUP | 양방향 조회, 오류 대체값 내장 | Microsoft 365 / 2021 이상 |
INDEX와 MATCH는 구버전 엑셀에서도 쓸 수 있는 조합입니다. =INDEX(가져올범위, MATCH(찾을값, 찾을범위, 0)) 형태로, 찾는 열과 가져오는 열을 따로 지정하기 때문에 위치 제약이 없습니다. 또 중간에 열을 삽입해도 결과가 어긋나지 않습니다.
XLOOKUP은 =XLOOKUP(찾을값, 찾을범위, 가져올범위, "없음") 형태입니다. 열 번호를 셀 필요가 없고, 네 번째 인수에 못 찾았을 때 표시할 값을 바로 넣을 수 있어 IFERROR로 감쌀 필요도 없습니다.
다만 XLOOKUP이 들어간 파일을 구버전 엑셀에서 열면 _xlfn.XLOOKUP이라는 오류로 표시됩니다. 파일을 다른 사람과 주고받는다면 상대방 버전을 먼저 확인하세요.
오류 표시를 정리하는 IFERROR
조회 대상에 없는 값이 정상적으로 존재하는 경우, #N/A가 화면을 채우면 보기 불편합니다. IFERROR로 감싸면 정리됩니다.
=IFERROR(VLOOKUP(A2,$E$2:$G$50,3,0),"")
따옴표 사이를 비우면 빈칸으로, 글자를 넣으면 그 글자가 표시됩니다.
단, 이것은 오류를 숨기는 기능입니다. 수식을 처음 작성하는 단계에서는 붙이지 마세요. #N/A가 보여야 무엇이 잘못됐는지 알 수 있습니다. 검증을 마친 뒤 마지막에 감싸는 것이 순서입니다.
인수별 정확한 동작과 버전별 지원 범위는 공식 문서에서 확인할 수 있습니다.
정리
엑셀 VLOOKUP 사용법은 다음 순서로 접근하면 대부분 해결됩니다.
- 인수 네 개를 채우고, 마지막에 반드시 0을 입력한다
- 범위를 선택한 상태에서 F4를 눌러 절대참조로 고정한다
- #N/A가 남으면 공백·숫자 형식·병합 셀을 확인한다
- 기준 열이 왼쪽이 아니면 INDEX·MATCH 또는 XLOOKUP으로 바꾼다
- 검증이 끝난 뒤에만 IFERROR로 감싼다
자주 묻는 질문
Q. F4를 눌러도 달러 기호가 안 붙습니다.
노트북에서는 F4가 밝기나 음량 키로 동작하는 경우가 있습니다. Fn 키를 함께 눌러 보세요. 그래도 안 되면 $를 직접 입력해도 결과는 같습니다.
Q. 값은 나오는데 전부 다른 사람 데이터가 나옵니다.
네 번째 인수가 비어 있을 가능성이 큽니다. 생략하면 근사값 검색으로 동작해 가장 가까운 값을 가져옵니다. 0 또는 FALSE를 입력하세요.
Q. 열을 하나 추가했더니 결과가 전부 틀어졌습니다.
VLOOKUP의 열 번호는 고정된 숫자라 열이 삽입되면 가리키는 위치가 달라집니다. 표 구조가 자주 바뀐다면 INDEX·MATCH나 XLOOKUP으로 바꾸는 편이 안전합니다.
Q. 대소문자를 구분해서 찾을 수 있나요?
VLOOKUP은 영문 대소문자를 구분하지 않습니다. abc와 ABC를 같은 값으로 처리합니다. 구분이 필요하면 EXACT 함수를 조합해야 합니다.
Q. 찾은 값이 여러 개일 때 전부 가져올 수 있나요?
VLOOKUP은 조건에 맞는 첫 번째 값 하나만 반환합니다. 여러 건을 모두 가져오려면 필터, 피벗 테이블, 또는 Microsoft 365의 FILTER 함수를 사용해야 합니다.
