엑셀 VLOOKUP 사용법과 오류 5가지 해결 순서

엑셀 VLOOKUP은 =VLOOKUP(찾을값, 범위, 열번호, 0) 네 칸을 채우고, 범위를 F4로 고정하면 끝납니다.

함수가 어려운 게 아니라, 지켜야 할 제약 세 가지를 모르고 쓰다가 오류를 만나는 경우가 대부분이거든요.

그러니 지금 화면에 뜬 오류부터 확인하는 게 빠릅니다. #N/A냐 #REF!냐에 따라 봐야 할 곳이 완전히 달라지거든요. 아래 표에서 증상부터 찾아 보세요.

VLOOKUP 오류 종류별 원인 찾기 (#N/A · #REF! · #VALUE!)

화면에 뜨는 것	원인	해결
첫 줄만 맞고 아래는 #N/A	범위가 밀림	F4로 절대참조
전부 #N/A	값 불일치 또는 첫 열 규칙 위반	공백·형식·범위 확인
#REF!	열 번호가 범위의 열 개수보다 큼	열 번호를 다시 셈
#VALUE!	열 번호가 0 이하이거나 숫자가 아님	세 번째 인수 확인
#NAME?	함수 이름 오타	철자 확인
엉뚱한 값이 나옴	네 번째 인수를 비웠음	0 또는 FALSE 입력

엑셀 VLOOKUP 사용법, 인수 네 개 채우기

구조 자체는 이게 전부입니다.

=VLOOKUP(찾을값, 범위, 열번호, 0)

찾을값 — 조회 기준이 되는 값입니다. 사번이나 상품코드처럼 중복되지 않는 항목이 적합하죠.
범위 — 데이터가 들어 있는 표 전체를 잡습니다.
열번호 — 그 범위 안에서 가져올 열의 순번입니다.
네 번째 인수 — 0(FALSE)은 정확히 일치, 1(TRUE)은 근사값입니다.

이 네 가지로 끝난다는 건 마이크로소프트의 ‘VLOOKUP 함수’ 설명에도 그대로 나와 있는데요, 실제로 사람을 붙잡는 건 함수 구조가 아니라 아래 제약 세 가지입니다.

제약 1 — 찾을값은 범위의 맨 왼쪽 열에 있어야 합니다

VLOOKUP은 지정한 범위의 맨 왼쪽 열에서만 값을 찾습니다. 사번이 왼쪽, 이름이 오른쪽인 표라면 이름으로 사번을 찾는 건 불가능하죠.

범위를 다시 잡아 기준 열이 맨 왼쪽에 오게 하거나, 뒤에서 설명할 INDEX·MATCH나 XLOOKUP을 쓰면 됩니다.

제약 2 — 열 번호는 시트가 아니라 범위 기준으로 셉니다

열 번호는 시트 전체가 아니라 지정한 범위 안에서 셉니다. 범위를 E열부터 잡았다면 E가 1, F가 2, G가 3인 식이죠.

시트에서 C열이라는 이유로 3을 넣으면 전혀 다른 값이 나옵니다. 값이 나오긴 하는데 틀린 값이라 한참 뒤에야 발견되는 유형입니다.

제약 3 — 네 번째 인수는 비워 두지 않습니다

네 번째 인수를 생략하면 근사값 검색(TRUE)으로 동작합니다. 첫 열이 오름차순으로 정렬돼 있다고 가정하고 가장 가까운 값을 찾거든요.

정렬되지 않은 표에서는 엉뚱한 결과가 나옵니다. 구간별 등급 산정처럼 의도한 경우가 아니라면 항상 0을 입력하세요.

VLOOKUP 드래그하면 #N/A가 뜨는 이유 (절대참조 F4)

첫 행은 멀쩡한데 아래로 복사하면 #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이면 일치하지 않죠.

셀 왼쪽 위에 초록 삼각형이 보인다면, 셀을 선택했을 때 나타나는 경고 아이콘에서 숫자로 변환을 고르세요.

여러 셀을 한 번에 처리하려면 해당 열을 선택한 뒤 데이터 > 텍스트 나누기 > 마침을 실행하는 방법도 있습니다.

조회 값과 원본 데이터의 형식이 다르면 오류가 날 수 있다는 점은 마이크로소프트의 ‘#N/A 오류를 수정하는 방법’ 안내에서도 확인할 수 있습니다.

병합된 셀

범위 안에 병합된 셀이 있으면 병합 영역의 첫 칸에만 값이 있고 나머지는 빈칸입니다. 조회 대상 표에서는 병합을 풀고 값을 채워 두는 편이 안전하죠.

병합을 정리했는데도 표가 제대로 정렬되지 않는다면, 엑셀 정렬 안됨 원인별 해결 체크리스트에서 데이터 형식과 범위 점검 순서를 확인해 보세요.

VLOOKUP 다른 시트·다른 파일에서 값 가져오기

참조할 표가 다른 시트에 있다면 범위 앞에 시트 이름과 느낌표를 붙입니다.

=VLOOKUP(A2, Sheet2!$E$2:$G$50, 3, 0)

직접 타이핑하는 것보다, 범위를 지정할 차례에 해당 시트 탭을 클릭하고 마우스로 드래그하는 편이 오타가 없습니다.

시트 이름에 공백이 들어 있으면 '판매 내역'!$E$2:$G$50처럼 작은따옴표가 자동으로 붙습니다.

다른 엑셀 파일도 참조할 수 있지만, 원본 파일이 닫혀 있거나 경로가 바뀌면 수식이 깨집니다. 정기적으로 쓰는 자료라면 같은 파일 안에 시트로 두는 편이 안정적이에요.

조건이 두 개 이상일 때 VLOOKUP 쓰는 법

VLOOKUP은 조회 조건을 하나만 받습니다. 지점과 상품명을 동시에 만족하는 값을 찾는 식은 그대로는 처리되지 않죠.

가장 간단한 건 보조 열을 만드는 방법입니다. 빈 열에 =B2&C2처럼 두 값을 이어 붙이고, 그 열을 기준으로 조회합니다.

이때 보조 열이 범위의 맨 왼쪽에 오도록 배치해야 합니다. 첫 열 규칙은 여기서도 그대로 적용되거든요.

찾을값도 같은 방식으로 이어 붙입니다. =VLOOKUP(F2&G2, $A$2:$D$100, 4, 0) 형태가 되죠.

Microsoft 365나 Excel 2021 이상을 쓴다면 XLOOKUP으로 더 간단히 끝납니다. 바로 아래에서 이어서 보겠습니다.

VLOOKUP 대신 INDEX·MATCH나 XLOOKUP을 쓸 때

함수	가능한 것	필요한 버전
VLOOKUP	기준이 맨 왼쪽에 있을 때만	모든 버전
INDEX + MATCH	기준이 어느 열에 있어도 가능	모든 버전
XLOOKUP	양방향 조회, 오류 대체값 내장	Microsoft 365 / 2021 이상

INDEX와 MATCH는 구버전 엑셀에서도 쓸 수 있는 조합입니다. =INDEX(가져올범위, MATCH(찾을값, 찾을범위, 0)) 형태죠.

찾는 열과 가져오는 열을 따로 지정하니 위치 제약이 없고, 중간에 열을 끼워 넣어도 결과가 어긋나지 않습니다.

XLOOKUP은 =XLOOKUP(찾을값, 찾을범위, 가져올범위, "없음") 형태입니다. 열 번호를 셀 필요가 없고, 못 찾았을 때 표시할 값도 네 번째 인수에 바로 넣습니다.

그래서 IFERROR로 감쌀 일도 없는데요, 다만 XLOOKUP이 들어간 파일을 구버전 엑셀에서 열면 _xlfn.XLOOKUP 오류로 표시됩니다. 파일을 주고받는다면 상대방 버전부터 확인하세요.