엑셀 VLOOKUP 함수 사용법 총정리 (정확히 일치, #N/A 오류 해결, XLOOKUP)

이 글의 치트키

• VLOOKUP은 =VLOOKUP(찾을 값, 표 범위, 가져올 열 번호, FALSE) 네 칸만 기억하면 된다
• 마지막 인수는 무조건 FALSE(정확히 일치). 생략하면 엉뚱한 값이 나온다
• 표 범위는 F4로 $G$2:$J$12처럼 고정해야 아래로 채워도 안 밀린다
• #N/A는 IFERROR(…,"미등록")으로 감싸 깔끔하게, 엑셀 2021·365라면 XLOOKUP이 더 간단하다

교육 수강자 명단에 부서·직급을 채워야 하는데, 사원 명단을 옆에 띄워 놓고 한 명씩 찾아 옮겨 적고 계신가요? 사람이 10명만 넘어가도 시간이 꽤 들고 오타도 생기죠. 오늘은 엑셀 VLOOKUP 함수로 다른 표에서 값을 자동으로 찾아오는 방법을 정리해 볼게요. #N/A 오류 처리와 요즘 많이 쓰는 XLOOKUP까지 한 번에 보여 드릴게요.

엑셀 VLOOKUP 함수란?

VLOOKUP은 기준 값(예: 사번)으로 다른 표에서 같은 행을 찾아, 그 행의 원하는 열 값을 가져오는 함수예요. V는 Vertical(세로)의 약자로, 표의 첫 번째 열을 위에서 아래로 훑으며 찾아요.

VLOOKUP 기본 구조

=VLOOKUP(찾을 값, 표 범위, 가져올 열 번호, FALSE)

오늘 예제는 왼쪽의 교육 수강자 명단 12명(A~E열)과 오른쪽의 사원 명단 11명(G~J열)이에요. 사번으로 사원 명단을 찾아 D열 부서, E열 직급을 채워 볼게요. 사번 A1050(한지아)은 일부러 사원 명단에 없는 사람으로 넣어 두었어요.

방법 1. VLOOKUP 기본 사용법 (정확히 일치)

가장 기본이 되는 방법이에요. 부서(D열)를 채워 볼게요.

STEP 1

D2 셀에 VLOOKUP 수식을 입력해요

찾을 값은 사번 A2, 표 범위는 사원 명단 $G$2:$J$12, 부서는 표의 세 번째 열(G=1, H=2, I=3)이라 3, 마지막은 정확히 일치를 뜻하는 FALSE예요.

D2 수식

=VLOOKUP(A2,$G$2:$J$12,3,FALSE)

STEP 2

Enter를 눌러 결과를 확인해요

사번 A1023 김도윤의 부서 재무팀이 들어왔어요. 사원 명단이 사번 순서대로 정렬되어 있지 않아도 FALSE를 쓰면 정확히 찾아와요.

STEP 3

채우기 핸들을 더블클릭해 아래로 복사해요

D2 셀 오른쪽 아래 작은 네모(채우기 핸들)를 더블클릭하면 D13까지 한 번에 채워져요. 그런데 사원 명단에 없는 A1050 행(D9)에는 #N/A가 떠요. 이건 방법 2에서 처리할게요.

주의

표 범위에 $를 붙이지 않고 G2:J12로 쓰면 아래로 채울 때 범위가 G3:J13, G4:J14…로 같이 밀려 내려가서 아래쪽 행에서 #N/A가 생겨요. 범위를 입력한 직후 F4를 한 번 누르면 $가 자동으로 붙어요.

방법 2. #N/A 오류를 '미등록'으로 바꾸기 (IFERROR)

#N/A는 "찾는 값이 표에 없다"는 뜻이에요. 틀린 건 아니지만 보고서에 그대로 두면 보기 안 좋고, 합계나 개수를 셀 때도 오류가 번져요. VLOOKUP을 IFERROR로 감싸면 오류 대신 원하는 글자를 보여 줄 수 있어요.

STEP 1

D2 수식을 IFERROR로 감싸요

기존 수식 앞에 IFERROR(, 뒤에 ,"미등록")을 붙이면 돼요.

D2 수식

=IFERROR(VLOOKUP(A2,$G$2:$J$12,3,FALSE),"미등록")

STEP 2

다시 아래로 채우고 D9를 확인해요

채우기 핸들을 더블클릭하면 #N/A였던 D9에 미등록이 표시돼요. 나머지 11명의 부서는 그대로예요.

실무대리 TIP

"미등록" 대신 ""(빈 따옴표)를 넣으면 빈칸으로 보여요. 다만 빈칸으로 숨기면 누락된 사람을 놓치기 쉬우니, 명단 대조가 목적이라면 눈에 띄는 글자로 표시하는 편이 안전해요.

반응형

방법 3. XLOOKUP으로 더 간단하게 찾기

엑셀 2021이나 Microsoft 365를 쓴다면 XLOOKUP이 더 편해요. 열 번호를 세지 않아도 되고, 못 찾았을 때 표시할 값도 함수 안에 바로 넣을 수 있어요. 이번엔 직급(E열)을 채워 볼게요.

STEP 1

E2 셀에 XLOOKUP 수식을 입력해요

찾을 값 A2, 찾을 범위 사번 열 $G$2:$G$12, 가져올 범위 직급 열 $J$2:$J$12, 못 찾으면 "미등록" 순서예요.

E2 수식

=XLOOKUP(A2,$G$2:$G$12,$J$2:$J$12,"미등록")

STEP 2

아래로 채우면 직급까지 완성돼요

E2의 채우기 핸들을 더블클릭하면 E13까지 직급이 채워지고, A1050 행은 IFERROR 없이도 미등록으로 나와요.

참고

XLOOKUP은 엑셀 2019 이하 버전에서는 #NAME? 오류가 나요. 파일을 여러 사람과 주고받는다면 상대방 버전을 확인하고, 모르겠으면 방법 2(IFERROR+VLOOKUP)를 쓰는 게 무난해요.

FALSE를 빼먹으면 생기는 일

VLOOKUP에서 가장 많이 하는 실수가 네 번째 인수를 생략하는 거예요. 생략하면 엑셀은 TRUE(유사 일치)로 처리하는데, 이 방식은 표가 오름차순으로 정렬되어 있다고 가정하고 "가장 가까운 값"을 가져와요. 아래 L열은 같은 수식에서 FALSE만 뺀 결과예요.

L2 수식 (잘못된 예)

=VLOOKUP(A2,$G$2:$J$12,3)

주의

김도윤(A1023)은 재무팀인데 L열에는 인사팀이, 정민준(A1002)은 총무팀인데 #N/A가 나왔어요. 오류가 아니라 그럴듯한 틀린 값이 나오기 때문에 더 위험해요. 사번·이름·코드를 찾을 때는 반드시 FALSE(또는 0)를 넣으세요.

완성 결과 비교

BEFORE

사원 명단을 보며 12명의 부서·직급을 손으로 옮겨 적어야 하는 명단

AFTER

사번만 있으면 부서·직급이 자동으로 채워지고, 명단에 없는 사람은 '미등록'으로 표시

상황별로 어떤 방법을 쓸까?

상황	추천 방법	수식 예
사번·코드로 정확히 찾기	방법 1 VLOOKUP + FALSE	=VLOOKUP(A2,$G$2:$J$12,3,FALSE)
없는 값이 섞여 있음	방법 2 IFERROR로 감싸기	=IFERROR(VLOOKUP(…),"미등록")
엑셀 2021·365 사용	방법 3 XLOOKUP	=XLOOKUP(A2,$G$2:$G$12,$J$2:$J$12,"미등록")
찾을 값보다 왼쪽 열을 가져와야 함	XLOOKUP (VLOOKUP 불가)	=XLOOKUP(값,찾을 열,왼쪽 열)
파일을 구버전 사용자와 공유	방법 1·2 VLOOKUP	XLOOKUP은 #NAME? 오류

같이 쓰면 좋은 단축키

F4 범위에 $ 절대참조 붙이기

F2 셀 수식 편집 모드 (참조 범위가 색으로 표시됨)

Ctrl + D 선택 범위에 위 셀 수식 아래로 채우기

Shift + F3 함수 마법사 열기

자주 묻는 질문

Q1. 값이 분명히 있는데 #N/A가 나와요.

대부분 눈에 안 보이는 차이 때문이에요. 사번 뒤에 공백이 붙어 있거나, 한쪽은 숫자이고 다른 쪽은 텍스트로 저장된 경우가 흔해요. 찾을 값을 TRIM(A2)로 감싸 공백을 없애거나, 숫자/텍스트 형식을 한쪽으로 맞춰 보세요. 셀 왼쪽 위에 초록 삼각형이 있다면 '텍스트 형식 숫자'일 가능성이 커요.

Q2. 열 번호를 매번 세기 귀찮아요. 다른 방법이 있나요?

XLOOKUP은 가져올 열을 범위로 직접 지정하니 열 번호가 필요 없어요. VLOOKUP만 써야 한다면 열 번호 자리에 MATCH("부서",$G$1:$J$1,0)를 넣으면 머리글 이름으로 열 번호를 자동으로 찾아 줘서, 중간에 열이 추가돼도 결과가 틀어지지 않아요.

예제 파일

오늘 쓴 교육 수강자 명단과 사원 명단을 그대로 받아서 연습해 보세요. 연습 시트에서 D·E열을 직접 채워 보고, 정답 시트의 수식과 비교하면 돼요.

엑셀_VLOOKUP_예제.xlsx

0.01MB

오늘의 한 줄 정리

=IFERROR(VLOOKUP(찾을 값,$G$2:$J$12,열 번호,FALSE),"미등록") 이 한 줄이면 명단 대조는 끝. 엑셀 365라면 XLOOKUP으로 더 짧게 쓰세요.