같은 시트에서는 잘 되던 VLOOKUP 수식이 다른 시트를 참조하는 순간부터 왜인지 #N/A 오류가 뜨는 경험, 해보셨나요? 막상 수식을 들여다봐도 어디가 문제인지 바로 안 보여서 답답한 경우가 많습니다.
이 글에서는 VLOOKUP 함수가 무엇인지부터 시작해, 다른 시트 참조 시 자주 발생하는 오류 원인 4가지와 해결법을 직접 테스트한 내용을 바탕으로 정리했습니다.
VLOOKUP 함수란? 이렇게 사용하세요
특정 값을 기준으로 표에서 원하는 데이터를 찾아 가져오는 함수입니다.
V는 Vertical(세로)의 약자로, 세로 방향으로 데이터를 검색합니다.
언제 씁니까?
실무에서 이런 상황에 자주 씁니다.
기본 문법
| 인수 | 설명 | 예시 |
|---|---|---|
| 찾을값 필수 | 찾으려는 값 또는 셀 주소 | A2 (사번이 입력된 셀) |
| 범위 필수 | 데이터가 있는 표 범위. 찾을 값은 반드시 이 범위의 첫 번째 열에 있어야 함 | $A$2:$D$100 |
| 열번호 필수 | 범위에서 몇 번째 열의 값을 가져올지 (첫 번째 열 = 1) | 2 (두 번째 열) |
| 일치옵션 선택 | FALSE(또는 0): 정확히 일치 / TRUE(또는 1): 유사 일치. 실무에서는 보통 FALSE 사용 | FALSE |
사번(A2)으로 직원 이름을 가져오는 기본 예시입니다.
→ A2의 값을 B2:D100 범위 첫 열에서 찾아, 첫 번째 열 값을 반환
다른 시트 참조 기본 문법
다른 시트의 데이터를 참조할 때는 범위 앞에 ‘시트명’!을 붙입니다. 같은 시트에서 쓸 때와 딱 이 부분만 다릅니다.
예를 들어 “직원정보”라는 시트의 A2:D100 범위에서 이름(2열)을 가져오려면 이렇게 씁니다.
다른 시트 참조할 때 막히는 이유 4가지
VLOOKUP 수식을 아래로 자동채우기(드래그)하면, 절대참조가 없을 경우 참조 범위가 한 칸씩 아래로 밀립니다. 찾아야 할 데이터가 범위 밖으로 벗어나면서 #N/A 오류가 발생합니다.
| A (찾을값) | B ❌ 절대참조 없음 | C ✅ 절대참조 있음 | |
|---|---|---|---|
| 2 | 1001 | =VLOOKUP(A2,’직원정보’!A2:D10,2,0) | =VLOOKUP(A2,’직원정보’!$A$2:$D$10,2,0) |
| 3 | 1002 | #N/A ← 범위가 A3:D11로 밀림 | 김철수 (정상) |
| 4 | 1003 | #N/A ← 범위가 A4:D12로 밀림 | 이영희 (정상) |
해결 방법
- 1수식에서 범위 부분(예: A2:D10)에 커서를 놓습니다
- 2F4 키를 한 번 누르면 자동으로 $가 붙어 절대참조로 바뀝니다
- 3‘직원정보’!A2:D10 → ‘직원정보’!$A$2:$D$10 으로 변경됐는지 확인합니다
시트 이름에 공백이 포함되어 있으면 반드시 작은따옴표(‘)로 감싸야 합니다. 직접 타이핑할 때 빠뜨리면 수식 오류가 발생합니다.
| 시트 이름 | 수식 | 결과 |
|---|---|---|
| 직원정보 (공백 없음) | =VLOOKUP(A2,직원정보!$A$2:$D$10,2,0) | 정상 작동 |
| 직원 정보 (공백 있음) | =VLOOKUP(A2,직원 정보!$A$2:$D$10,2,0) | 오류 발생 |
| 직원 정보 (공백 있음) | =VLOOKUP(A2,’직원 정보’!$A$2:$D$10,2,0) | 정상 작동 |
찾을 값은 숫자(1001)인데 참조 시트의 데이터는 텍스트(“1001”)로 저장된 경우, 눈에는 같아 보여도 엑셀은 다른 값으로 인식해서 #N/A 오류를 반환합니다. 시각적으로 차이가 없어서 원인을 찾기 가장 어려운 케이스입니다.
| 확인 방법 | 숫자로 저장된 경우 | 텍스트로 저장된 경우 |
|---|---|---|
| 셀 내 정렬 | 오른쪽 정렬 | 왼쪽 정렬 |
| 셀 좌상단 표시 | 없음 | 녹색 삼각형(▲) 표시 |
| 셀 서식(Ctrl+1) | 일반 또는 숫자 | 텍스트 |
해결 방법 (Microsoft 공식 문서 기준)
- 1텍스트로 저장된 열 전체를 선택합니다
- 2데이터 탭 → 텍스트 나누기 → 마침 클릭 (텍스트를 숫자로 변환)
- 3또는 VLOOKUP 수식 안에서 형식을 통일합니다
=VLOOKUP(TEXT(A2,”0″), ‘직원정보’!$A$2:$D$10, 2, FALSE)
텍스트를 숫자로 변환해서 맞출 때:
=VLOOKUP(VALUE(A2), ‘직원정보’!$A$2:$D$10, 2, FALSE)
VLOOKUP은 반드시 참조 범위의 첫 번째 열(맨 왼쪽)에서 값을 찾습니다. 사번이 B열에 있는데 범위를 A열부터 지정하면 엑셀은 A열에서 찾으므로 #N/A가 납니다. 이건 VLOOKUP의 구조적 한계입니다. (출처: Microsoft 공식 문서 — VLOOKUP 함수)
| A열 (이름) | B열 (사번) ← 찾을 값 | C열 (부서) | D열 (연봉) | |
|---|---|---|---|---|
| 2 | 김철수 | 1001 | 개발팀 | 4200만 |
| 3 | 이영희 | 1002 | 마케팅팀 | 3800만 |
=VLOOKUP(1001, ‘직원정보’!$A$2:$D$10, 3, FALSE)
✅ 해결: 범위를 사번(B열)부터 시작하면 정상 작동
=VLOOKUP(1001, ‘직원정보’!$B$2:$D$10, 2, FALSE)
업그레이드: XLOOKUP으로 교체하는 법
엑셀 2021 이상 또는 Microsoft 365를 사용 중이라면 VLOOKUP 대신 XLOOKUP을 쓰면 위에서 설명한 원인 4번(첫 번째 열 제한) 문제가 구조적으로 해결됩니다.
| 항목 | VLOOKUP | XLOOKUP |
|---|---|---|
| 검색 방향 | 왼→오른쪽만 가능 | 어느 방향이나 가능 |
| 찾을 값 위치 | 반드시 범위 첫 번째 열 | 제한 없음 |
| #N/A 기본 처리 | IFERROR 별도 중첩 필요 | 네 번째 인수에 포함 |
| 사용 가능 버전 | 모든 버전 | 엑셀 2021 / Microsoft 365 이상 |
=XLOOKUP(찾을값, 찾을범위, 반환범위, “없을때표시”, 0)
예시: 사번(A2)으로 이름을 가져올 때
=XLOOKUP(A2, ‘직원정보’!$B$2:$B$100, ‘직원정보’!$A$2:$A$100, “없음”, 0)
이전 버전(2016, 2019)을 사용 중이라면 VLOOKUP 또는 INDEX+MATCH 조합을 사용해야 합니다. (출처: Microsoft 공식 문서 — XLOOKUP 함수)
📌 오늘 핵심 정리
- VLOOKUP은 표에서 특정 값을 기준으로 데이터를 가져오는 함수. 실무에서 가장 많이 쓰는 참조 함수 중 하나
- 다른 시트 참조 시 범위 앞에 ‘시트명’! 을 붙여야 함 (마우스 클릭으로 자동 입력 가능)
- 자동채우기 시 범위가 밀린다면 → F4 키로 절대참조($) 적용
- 시트명에 공백이 있으면 → 작은따옴표(‘)로 감싸기
- 같은 숫자인데 #N/A가 난다면 → 형식 불일치 (텍스트 나누기로 해결)
- 찾을 값이 첫 번째 열에 없어 안 된다면 → 범위 시작 열 조정 또는 XLOOKUP 사용
· Microsoft 공식 문서 — VLOOKUP 함수: support.microsoft.com
· Microsoft 공식 문서 — VLOOKUP에서 발생하는 #N/A 오류 수정 방법: support.microsoft.com
· Microsoft 공식 문서 — XLOOKUP 함수: support.microsoft.com
· Automate Excel — VLOOKUP #N/A 오류 처리: automateexcel.com