VLOOKUP·XLOOKUP 사용법: 구문과 예시, #N/A 오류 해결, 절대참조까지
TUREFF• •0
다른 표에서 값을 찾아오는 일은 엑셀 업무에서 가장 자주 하는 작업입니다. 사번으로 이름을 찾고, 상품코드로 단가를 가져오는 식이죠. 오래된 VLOOKUP과 새로 나온 XLOOKUP의 쓰는 법, 그리고 결과가 #N/A로 나올 때 확인할 것을 정리했습니다.
VLOOKUP 구문
=VLOOKUP(찾을 값, 찾을 범위, 가져올 열 번호, 일치 방식)
- 찾을 값: 기준이 되는 값입니다. 보통 셀 하나(예: A2)를 넣습니다.
- 찾을 범위: 찾을 값이 들어 있는 열이 반드시 범위의 맨 왼쪽 열이어야 합니다.
- 열 번호: 범위의 왼쪽에서 몇 번째 열의 값을 가져올지 숫자로 씁니다. 맨 왼쪽이 1입니다.
- 일치 방식: 정확히 같은 값을 찾으려면 FALSE(또는 0)를 씁니다.
VLOOKUP 예시
D열에 상품코드, E열에 단가가 있는 단가표(D2:E100)가 있고, A2의 상품코드로 단가를 찾고 싶다면 이렇게 씁니다.
=VLOOKUP(A2,$D$2:$E$100,2,FALSE)
범위 D2:E100에서 D열이 1번째, E열이 2번째 열이므로 열 번호는 2입니다. 이 수식을 아래로 채우기 하면 A3, A4로 찾을 값은 바뀌고 단가표 범위는 고정됩니다.
자주 나는 오류와 해결법
- 마지막 인수를 비워 두기: 생략하면 TRUE(근사 일치)로 동작해, 정렬되지 않은 표에서 엉뚱한 값이 나옵니다. 코드나 이름을 찾을 때는 꼭 FALSE를 쓰세요.
- 절대참조를 안 걸기: 범위를 D2:E100처럼 쓰고 아래로 끌면 D3:E101, D4:E102로 밀려 내려가 아래쪽에서 #N/A가 납니다. 범위를 선택한 상태에서 F4를 누르면 $D$2:$E$100으로 고정됩니다.
- #N/A: 찾는 값이 범위에 없다는 뜻입니다. 앞뒤 공백, 숫자가 텍스트로 저장된 경우(셀 왼쪽 위 초록 삼각형), 오타를 먼저 확인하세요. TRIM 함수로 공백을 지우거나, 텍스트로 된 숫자를 숫자로 변환하면 해결되는 경우가 많습니다.
- #REF!: 열 번호가 범위의 열 개수보다 큽니다.
- 왼쪽 값을 못 찾음: VLOOKUP은 오른쪽 방향으로만 찾습니다. 찾을 열보다 왼쪽에 있는 값이 필요하면 XLOOKUP이나 INDEX와 MATCH 조합을 씁니다.
- 열을 끼워 넣으면 결과가 바뀜: 열 번호가 숫자로 고정돼 있어서, 중간에 열을 추가하면 다른 열을 가져옵니다.
XLOOKUP 구문과 장점
=XLOOKUP(찾을 값, 찾을 열, 가져올 열, 없을 때 표시할 값)
같은 예시를 XLOOKUP으로 쓰면 이렇습니다.
=XLOOKUP(A2,$D$2:$D$100,$E$2:$E$100,"없음")
- 기본이 정확히 일치라 FALSE를 깜빡할 일이 없습니다.
- 찾을 열과 가져올 열을 따로 지정하니 왼쪽 방향 조회도 됩니다.
- 열 번호를 세지 않아도 되고, 열을 끼워 넣어도 결과가 바뀌지 않습니다.
- 네 번째 인수로 값이 없을 때 보여 줄 문구를 바로 정할 수 있어 IFERROR로 감쌀 필요가 줄어듭니다.
- 다섯·여섯 번째 인수로 근사 일치나 아래에서 위로 찾기(가장 마지막 값 찾기)도 할 수 있습니다.
내 엑셀에서 XLOOKUP이 될까?
XLOOKUP은 Microsoft 365, Excel 2021, Excel 2024, 웹용 Excel에서 쓸 수 있습니다. Excel 2019와 2016 이하에는 없어서 그 버전에서 파일을 열면 #NAME? 오류가 납니다. 버전은 파일 메뉴의 계정 화면에서 제품 정보로 확인할 수 있습니다.
파일을 여러 사람이 다른 버전으로 함께 쓴다면 VLOOKUP이나 INDEX와 MATCH 조합이 아직 안전합니다. 혼자 쓰거나 회사 전체가 Microsoft 365라면 XLOOKUP으로 바꾸는 편이 실수가 훨씬 적습니다.
정리
- 정확히 찾을 때는 VLOOKUP 마지막 인수에 FALSE
- 범위는 F4로 절대참조
- #N/A가 나오면 공백과 텍스트형 숫자부터 확인
- 버전이 된다면 XLOOKUP
VLOOKUP이나 XLOOKUP을 쓰다가 풀리지 않은 오류가 있다면 수식과 상황을 댓글로 남겨 주세요. 비슷한 문제를 겪은 분들이 함께 답을 찾아 드릴 수 있습니다.