XLOOKUP과 VLOOKUP 차이 — 무엇이 다르고 언제 바꿔 써야 하나(예제·변환 규칙)
=VLOOKUP(찾는값, 표 범위, 열 번호, FALSE)처럼 표 하나와 열 번호로 찾고, XLOOKUP은 =XLOOKUP(찾는값, 찾을 범위, 가져올 범위)처럼 범위 두 개로 찾습니다. 그래서 XLOOKUP은 왼쪽 열 값도 가져오고, 기본이 정확히 일치이며, 못 찾을 때 보일 값을 바로 지정할 수 있습니다. 다만 Excel 2016·2019에서는 XLOOKUP을 쓸 수 없습니다.아래 예시는 모두 A1:D5에 다음 표가 있다고 가정합니다. 일부러 코드가 B열, 품명이 그 왼쪽 A열에 있게 만들었습니다.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 품명 | 코드 | 단가 | 재고 |
| 2 | 연필 | P001 | 500 | 120 |
| 3 | 노트 | P002 | 1500 | 40 |
| 4 | 지우개 | P003 | 300 | 75 |
| 5 | 가위 | P004 | 2500 | 0 |
한눈에 보는 차이
| 항목 | VLOOKUP | XLOOKUP |
|---|---|---|
| 가져올 위치 지정 | 표 범위 안의 열 번호(숫자) | 가져올 범위를 직접 지정 |
| 찾는 열의 위치 | 표 범위의 맨 왼쪽 열이어야 함 | 어느 열이든 됨(왼쪽 값도 가져옴) |
| 마지막 인수를 생략하면 | 대략 일치(TRUE) | 정확히 일치(0) |
| 못 찾았을 때 | #N/A → IFNA나 IFERROR로 감싸야 함 | 4번째 인수에 보일 값을 지정 |
| 찾는 방향 | FALSE(정확히 일치): 위에서 아래로 처음 나온 값TRUE·생략(대략 일치): 오름차순 정렬을 전제로 한 근사 일치(같은 값이 여럿이면 다른 행이 나올 수 있음) | 기본은 위에서부터 처음 나온 값, 아래에서부터 찾으면 마지막 값 |
와일드카드(* ?) | 정확히 일치(FALSE)일 때 그대로 적용 | 일치 유형을 2로 줘야 적용 |
| 여러 열 한꺼번에 | 보통 열마다 수식 하나씩(열 번호에 {2,3} 같은 배열을 넣는 방법도 있음) | 수식 하나로 이웃한 여러 열을 돌려줌 |
| 사용 가능한 버전 | 대부분의 버전 | Microsoft 365, Excel 2021·2024 등(2016·2019 불가) |
구조 차이: 열 번호 대신 범위
마이크로소프트 문서의 형식은 다음과 같습니다. 대괄호 안 인수는 생략할 수 있습니다.
=VLOOKUP(찾는값, 표 범위, 열 번호, [일치 유형])
=XLOOKUP(찾는값, 찾을 범위, 가져올 범위, [못 찾을 때 값], [일치 유형], [검색 방향])코드 P003의 단가를 두 함수로 구하면 이렇습니다.
| 함수 | 수식 | 결과 |
|---|---|---|
| VLOOKUP | =VLOOKUP("P003",B2:D5,2,FALSE) | 300 |
| XLOOKUP | =XLOOKUP("P003",B2:B5,C2:C5) | 300 |
VLOOKUP은 찾는 열 B가 범위의 첫 열이 되도록 B2:D5를 잡고, 단가가 그 안에서 2번째 열이라 2를 씁니다. XLOOKUP은 「B열에서 찾아 C열 값을 가져와라」라고 범위 두 개를 적을 뿐입니다. 정확히 일치가 기본이라 FALSE 같은 마지막 인수도 필요 없습니다.
이 차이는 열을 끼워 넣을 때 드러납니다. B열과 C열 사이에 새 열을 넣으면 엑셀이 범위를 B2:E5로 넓혀 주지만, VLOOKUP의 열 번호 2는 숫자라서 그대로입니다. 그러면 2번째 열이 된 새로 넣은 빈 열을 가져옵니다. XLOOKUP의 C2:C5는 범위 참조라 단가 열을 따라 D2:D5로 함께 옮겨 갑니다.
B2:B5와 C2:C5처럼 같은 2~5행). 가져올 범위는 C2:D5처럼 여러 열이어도 됩니다. 행이 어긋나면 엉뚱한 행의 값을 가져오거나 오류가 납니다.왼쪽 열 값 가져오기
코드(B열)로 품명(A열)을 찾고 싶다고 합시다. 품명이 찾는 열의 왼쪽에 있습니다.
| 함수 | 수식 | 결과 |
|---|---|---|
| XLOOKUP | =XLOOKUP("P003",B2:B5,A2:A5) | 지우개 |
| VLOOKUP | 그대로는 불가 | — |
VLOOKUP은 찾는 값이 표 범위의 첫 번째 열에 있어야 하고, 값은 그 열이나 오른쪽 열에서만 가져옵니다. 그래서 열 순서를 바꾸거나 INDEX·MATCH 같은 다른 함수를 써야 했습니다. XLOOKUP은 찾을 범위와 가져올 범위를 따로 적으니 방향이 상관없습니다.
여러 열을 한 번에 가져올 수도 있습니다. =XLOOKUP("P003",B2:B5,C2:D5)를 한 칸에 입력하면 단가 300과 재고 75가 이웃한 두 칸에 함께 나옵니다. 결과가 퍼져 나갈 오른쪽 칸은 비워 두어야 하며, 무언가 들어 있으면 #SPILL! 오류가 납니다. 동적 배열을 지원하는 Excel이라면 VLOOKUP도 =VLOOKUP("P003",B2:D5,{2,3},FALSE)처럼 열 번호에 배열을 넣어 같은 결과를 낼 수 있지만, 가져올 범위를 그대로 적는 XLOOKUP 쪽이 읽기 쉽습니다.
못 찾았을 때 표시하기
없는 코드 P009를 찾으면 두 함수 모두 기본으로 #N/A를 냅니다. 「없음」이라고 보이게 하려면 이렇게 씁니다.
| 함수 | 수식 | 결과 |
|---|---|---|
| XLOOKUP | =XLOOKUP("P009",B2:B5,C2:C5,"없음") | 없음 |
| VLOOKUP | =IFNA(VLOOKUP("P009",B2:D5,2,FALSE),"없음") | 없음 |
| VLOOKUP | =IFERROR(VLOOKUP("P009",B2:D5,2,FALSE),"없음") | 없음 |
결과는 같아 보여도 덮는 범위가 다릅니다. IFNA는 #N/A만 바꿉니다. IFERROR는 #N/A뿐 아니라 #VALUE!·#REF!·#DIV/0!·#NUM!·#NAME?·#NULL! 같은 여러 수식 오류를 함께 「없음」으로 덮습니다. 그래서 열 번호를 잘못 써서 난 #REF!도 「없음」으로 보여 실수를 놓칠 수 있습니다. 다른 종류의 오류까지 숨기지 않으려면 IFNA가 IFERROR보다 처리 범위가 좁습니다. 다만 IFNA도 #N/A의 원인이 「항목을 못 찾음」인지 다른 곳의 #N/A인지는 구분하지 않고 모두 바꿉니다. XLOOKUP의 4번째 인수는 일치 항목을 못 찾았을 때만 쓰입니다.
구간 찾기와 마지막 값 찾기
XLOOKUP의 5번째 인수(일치 유형)와 6번째 인수(검색 방향)는 다음 값을 받습니다.
| 인수 | 값 | 뜻 |
|---|---|---|
| 일치 유형 | 0 | 정확히 일치(기본값) |
-1 | 정확히 일치, 없으면 바로 아래(더 작은) 값 | |
1 | 정확히 일치, 없으면 바로 위(더 큰) 값 | |
2 | 와일드카드 일치 | |
| 검색 방향 | 1 | 첫 항목부터(기본값) |
-1 | 마지막 항목부터 거꾸로 | |
2 | 오름차순 정렬된 범위에서 이진 검색 | |
-2 | 내림차순 정렬된 범위에서 이진 검색 |
점수 구간 등급: G2:G6에 구간 시작 점수 0·60·70·80·90, H2:H6에 등급 F·D·C·B·A가 있다고 합시다.
| 수식 | 결과 | 설명 |
|---|---|---|
=VLOOKUP(85,G2:H6,2,TRUE) | B | 85 이하 중 가장 큰 80의 등급 |
=XLOOKUP(85,G2:G6,H2:H6,,-1) | B | 같은 뜻(일치 유형 -1) |
=XLOOKUP(59,G2:G6,H2:H6,,-1) | F | 59 이하 중 가장 큰 0의 등급 |
=XLOOKUP(85,G2:G6,H2:H6,,1) | A | 85 이상 중 가장 작은 90의 등급 |
=XLOOKUP(95,G2:G6,H2:H6,,1) | #N/A | 95 이상인 값이 없음 |
4번째 인수 자리를 비워 두려고 쉼표를 두 번(,,) 썼습니다. 마이크로소프트 문서는 VLOOKUP의 대략 일치(TRUE)를 쓰기 전에 첫 열을 정렬하라고 합니다. 위 예처럼 작은 값부터(오름차순) 정렬해 두어야 하며, 큰 값부터 정렬하면 엉뚱한 결과가 나올 수 있습니다. XLOOKUP은 기본 검색 방향에서는 정렬하지 않아도 되지만, 검색 방향을 이진 검색(2·-2)으로 고를 때 정렬이 필요하며 정렬되지 않으면 잘못된 결과가 나온다고 적습니다.
마지막 값 찾기: J2:K6에 입고 기록이 날짜순으로 쌓여 있고 같은 품명이 여러 번 나온다고 합시다.
| J(품명) | K(입고 단가) |
|---|---|
| 연필 | 450 |
| 노트 | 1400 |
| 연필 | 480 |
| 가위 | 2500 |
| 연필 | 500 |
| 수식 | 결과 | 설명 |
|---|---|---|
=VLOOKUP("연필",J2:K6,2,FALSE) | 450 | 처음 나온 연필 |
=XLOOKUP("연필",J2:J6,K2:K6) | 450 | 처음 나온 연필 |
=XLOOKUP("연필",J2:J6,K2:K6,,0,-1) | 500 | 마지막(가장 최근) 연필 |
와일드카드는 반대로 동작
바꿔 쓸 때 가장 놓치기 쉬운 차이입니다. *는 0개 이상의 문자, ?는 한 글자를 뜻합니다.
| 수식 | 결과 | 설명 |
|---|---|---|
=VLOOKUP("연*",A2:C5,3,FALSE) | 500 | 정확히 일치 + 텍스트면 와일드카드가 바로 적용 |
=XLOOKUP("연*",A2:A5,C2:C5) | #N/A | 기본(0)에서는 「연*」이라는 글자 자체를 찾음 |
=XLOOKUP("연*",A2:A5,C2:C5,,2) | 500 | 일치 유형 2를 줘야 와일드카드로 해석 |
반대로 VLOOKUP에서 실제 별표나 물음표가 든 값을 찾으려면 앞에 물결표를 붙여야 합니다. 실제 별표는 ~*, 실제 물음표는 ~?로 적습니다. 찾는값을 셀(예: E2)로 넘길 때도 마찬가지라, 그 칸에 *·?가 들어 있으면 VLOOKUP은 와일드카드로 찾고 XLOOKUP(기본)은 글자 그대로 찾습니다.
VLOOKUP을 XLOOKUP으로 바꾸는 규칙
수식을 자동으로 바꿔 주는 도구는 찾는값이 셀 참조인지, 별표가 들어 있는지에 따라 결과가 달라질 수 있어서, 여기서는 변환 규칙과 검산한 예로 정리합니다. 위의 A1:D5 표를 기준으로 했습니다.
| VLOOKUP의 요소 | XLOOKUP에서는 |
|---|---|
| 표 범위의 첫 열 | 「찾을 범위」(그 열만 적습니다) |
| 열 번호 k | 표 범위의 첫 열에서 k번째 열을 「가져올 범위」로 직접 적습니다(같은 행 범위) |
마지막 인수 FALSE(정확히 일치) | 생략합니다(기본이 정확히 일치) |
마지막 인수 TRUE이거나 생략(대략 일치) | 5번째 인수를 -1로 줍니다 — 첫 열이 오름차순으로 정렬돼 있고 값이 겹치지 않을 때 같은 결과를 기대할 수 있습니다. 같은 값이 여러 행이면 결과가 달라질 수 있어 행별로 확인하세요 |
원래 수식이 FALSE이고 찾는값의 * ?를 와일드카드로 쓰던 경우(셀 참조 속 값 포함) | 5번째 인수를 2로 줘야 같은 방식으로 찾습니다. 실제 별표·물음표를 ~*·~?로 찾던 수식은 =XLOOKUP("~*",찾을 범위,가져올 범위,,2)처럼 2를 주거나, 기본 모드에서 ~를 지운 "*"로 적습니다(셀 속 값도 같은 검토가 필요합니다). TRUE·생략 수식은 이 규칙을 쓰지 않습니다 |
IFNA(VLOOKUP(...),"없음") | 「못 찾은 경우」만 처리하려던 것이면 4번째 인수 "없음"으로 대신합니다. 일치한 칸 자체의 #N/A 등 원래 IFNA가 바꾸던 것을 그대로 유지하려면 =IFNA(XLOOKUP(...),"없음")으로 바꿉니다 |
| VLOOKUP | 바꾼 XLOOKUP | 결과 |
|---|---|---|
=VLOOKUP("P003",B2:D5,2,FALSE) | =XLOOKUP("P003",B2:B5,C2:C5) | 300 |
=VLOOKUP(E2,$A$2:$D$5,3,FALSE) (E2가 연필) | =XLOOKUP(E2,$A$2:$A$5,$C$2:$C$5) | 500 |
=VLOOKUP(85,G2:H6,2,TRUE) | =XLOOKUP(85,G2:G6,H2:H6,,-1) | B |
=IFNA(VLOOKUP("P009",B2:D5,2,FALSE),"없음") (못 찾은 경우 처리) | =XLOOKUP("P009",B2:B5,C2:C5,"없음") | 없음 |
=VLOOKUP("연*",A2:C5,3,FALSE) | =XLOOKUP("연*",A2:A5,C2:C5,,2) | 500 |
달러 표시($) 고정은 두 범위에 원래 수식에서 의도한 대로 각각 붙입니다. 두 번째 예처럼 $A$2:$D$5를 쪼갤 때는 찾을 범위와 가져올 범위 모두 행·열을 고정합니다. 바꾼 수식은 원래 수식과 같은 결과가 나오는지 몇 행 확인한 뒤 바꿔 쓰세요. 이름 정의나 표 이름을 쓴 수식은 해당 범위가 실제로 어디인지 확인하고 직접 고쳐야 합니다.
버전 확인과 어느 쪽을 쓸까
마이크로소프트 문서에 따르면 XLOOKUP은 Microsoft 365, Excel 2021·2024(Mac 포함)와 iPad·iPhone·Android용 Excel에서 쓸 수 있고, Excel 2016과 Excel 2019에서는 사용할 수 없습니다. 문서는 새 버전에서 만든 XLOOKUP 파일을 2016·2019에서 열게 되는 경우도 있다고 따로 적어 둡니다.
| 상황 | 권장 |
|---|---|
| 나와 파일을 받는 사람 모두 Microsoft 365 또는 2021 이후 | XLOOKUP |
| Excel 2016·2019를 쓰는 사람과 파일을 주고받음 | VLOOKUP(또는 INDEX·MATCH) |
| 이미 잘 돌아가는 VLOOKUP 수식 | 그대로 둬도 됨 — 고칠 일이 생길 때 바꾸기 |
마이크로소프트의 VLOOKUP 문서도 XLOOKUP을 「어느 방향으로나 작동하고 기본으로 정확한 일치 항목을 반환하는 개선된 버전」이라고 소개합니다. VLOOKUP 자체의 인수와 정확히 일치·대략 일치는 VLOOKUP 사용법에, #N/A 원인은 VLOOKUP이 #N/A만 나올 때에 정리해 두었습니다.