XLOOKUP vs VLOOKUP 차이점: 엑셀 함수 비교, 사용 예시, 무엇을 써야 할까

XLOOKUP과 VLOOKUP 중 무엇을 쓸지 고민된다면, 파일을 여는 모든 사람이 Microsoft 365 Excel, Excel 2021 이상, 웹용 Excel을 사용한다면 XLOOKUP을 쓰세요. VLOOKUP이 하는 일을 모두 하면서 실수는 더 적습니다. 기본이 정확히 일치이고, 왼쪽 열의 값도 찾을 수 있으며, 열을 삽입해도 깨지지 않습니다. 통합 문서가 Excel 2019 이하에서도 작동해야 할 때만 VLOOKUP을 사용하세요.

이 호환성 문제가 가장 중요한 주의점입니다. XLOOKUP은 Excel 2016과 2019에 없으므로, XLOOKUP 수식으로 가득한 파일을 이 버전으로 열면 #NAME? 오류가 표시됩니다.

이 글에서 수식은 영어 버전 Excel과 같은 형태로 표기합니다. 한국어 Excel에서도 함수 이름(VLOOKUP, XLOOKUP, INDEX, MATCH 등)은 영어 그대로 사용하므로 그대로 입력하면 됩니다. 한국어 Excel에서는 인수를 쉼표(,)로 구분합니다.

구문 나란히 비교하기

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

가장 큰 차이는 답이 있는 위치를 Excel에 알려 주는 방식입니다. VLOOKUP은 표 전체와, 그 표의 왼쪽 가장자리부터 센 열 번호를 받습니다. XLOOKUP은 검색할 열과 값을 반환할 열, 두 개의 별도 범위를 받습니다.

같은 조회를 두 가지 방식으로 작성하기

A1:D6에 간단한 제품 목록이 있고 G2에 제품 ID를 입력해 두었다고 가정합니다.

P-104의 가격을 찾으려면 다음과 같이 합니다.

=VLOOKUP(G2, A2:D6, 4, FALSE)
=XLOOKUP(G2, A2:A6, D2:D6)

두 수식 모두 $59.00을 반환합니다. VLOOKUP은 A2:D6의 첫 번째 열을 검색해 4번째 열을 반환하며, FALSE는 정확히 일치하도록 강제합니다. XLOOKUP은 A2:A6을 검색해 D2:D6에서 일치하는 셀을 반환하며, 정확히 일치가 기본값입니다.

수식을 열 아래로 복사할 예정이라면 =XLOOKUP(G2, $A$2:$A$6, $D$2:$D$6)처럼 절대 참조로 표를 고정하세요. 참조 안에 커서를 두고 Windows에서는 F4, Mac에서는 Cmd+T를 누르면 $ 기호가 추가됩니다.

VLOOKUP의 대표적인 함정 두 가지

1. FALSE(또는 0) 빠뜨리기

VLOOKUP의 마지막 인수는 선택 사항이며, 생략하면 Excel은 근사 일치를 사용합니다. 근사 일치에서 VLOOKUP은 첫 번째 열이 정렬되어 있다고 가정하고, 아무 경고 없이 검색한 값보다 작거나 같은 가장 가까운 값을 반환합니다.

위의 표에서 =VLOOKUP("P-110", A2:D6, 4)를 시험해 보세요. P-110은 존재하지 않지만 #N/A 대신 $32.75가 나옵니다. "P-105"가 가장 가까운 작은 값이어서 P-105의 가격이 반환된 것입니다. 정렬되지 않은 데이터에서는 결과가 더 제멋대로일 수 있습니다.

정확히 일치하는 VLOOKUP은 항상 FALSE 또는 0으로 끝내세요. XLOOKUP은 따로 요청하지 않는 한 정확히 일치를 사용하므로 이 문제가 없습니다.

2. 하드코딩된 열 번호

VLOOKUP 수식의 4는 그냥 숫자입니다. 누군가 Product와 Category 사이에 Supplier 열을 삽입하면 표가 A:E가 되고 Price가 E열로 이동하지만, 수식은 여전히 4번째 열을 반환하며 이제 그 열은 Category입니다. $59.00 대신 "Video"가 표시됩니다.

XLOOKUP은 가격 열(D2:D6) 자체를 가리키므로 Excel이 자동으로 E2:E6으로 업데이트하고, 답은 계속 정확합니다.

XLOOKUP만 할 수 있는 것

왼쪽 값 찾기

VLOOKUP은 찾는 열의 오른쪽에 있는 열만 반환할 수 있습니다. XLOOKUP은 방향을 가리지 않습니다. G2에 있는 제품 이름의 제품 ID를 찾으려면 다음과 같이 합니다.

=XLOOKUP(G2, B2:B6, A2:A6)

G2에 "Desk Lamp"가 있으면, A열이 B열의 왼쪽에 있는데도 P-105가 반환됩니다.

찾는 값이 없을 때 알기 쉬운 메시지 표시하기

=XLOOKUP(G2, A2:A6, D2:D6, "Not found")

VLOOKUP으로 같은 결과를 내려면 함수를 감싸야 합니다: =IFNA(VLOOKUP(G2, A2:D6, 4, FALSE), "Not found").

수식 하나로 여러 열 반환하기

=XLOOKUP(G2, A2:A6, B2:D6)

P-104의 경우 Webcam HD, Video, $59.00이 옆으로 나란한 세 셀에 스필됩니다. 해당 셀은 비워 두세요. 그렇지 않으면 #SPILL! 오류가 발생합니다.

첫 번째가 아닌 마지막 일치 항목 찾기

같은 고객이 여러 번 나타나는 로그에서는 search_mode를 -1로 설정해 아래에서부터 검색하면 가장 최근 항목을 가져옵니다.

=XLOOKUP(G2, A2:A500, C2:C500, "Not found", 0, -1)

정렬 없이 근사 일치 사용하기

단계별 가격이나 수수료율에서 VLOOKUP의 근사 일치는 첫 번째 열이 오름차순으로 정렬되어 있을 때만 작동합니다. XLOOKUP의 match_mode -1(정확히 일치하거나 다음으로 작은 항목)과 1(정확히 일치하거나 다음으로 큰 항목)은 정렬된 데이터가 필요하지 않습니다. J2:J5에 최소 주문 금액, K2:K5에 할인율이 있다고 하면 다음과 같습니다.

=VLOOKUP(B2, J2:K5, 2, TRUE)
=XLOOKUP(B2, J2:J5, K2:K5, , -1)

와일드카드

둘 다 *와 ?를 지원하지만, XLOOKUP은 이를 와일드카드로 취급하려면 match_mode 2가 필요합니다.

=VLOOKUP("Desk*", B2:D6, 3, FALSE)
=XLOOKUP("Desk*", B2:B6, D2:D6, , 2)

두 수식 모두 Desk Lamp의 $32.75를 반환합니다.

INDEX/MATCH: 고전적인 대안

XLOOKUP이 나오기 전에 VLOOKUP의 한계를 해결하는 표준 방법은 INDEX와 MATCH의 조합이었습니다. 왼쪽 값도 찾고, 열을 삽입해도 유지되며, 모든 Excel 버전에서 작동하므로 Excel 2019 이하에서도 열려야 하는 파일에 가장 좋은 선택입니다.

=INDEX(D2:D6, MATCH(G2, A2:A6, 0))
=INDEX(A2:A6, MATCH(G2, B2:B6, 0))

첫 번째 수식은 G2의 ID에 해당하는 가격을 반환하고, 두 번째 수식은 왼쪽 값 찾기 예제입니다. MATCH의 0은 정확히 일치를 뜻하며, VLOOKUP의 FALSE처럼 빠뜨리기 쉽습니다. XLOOKUP은 같은 아이디어를 더 짧고 안전하게 만든 버전입니다.

VLOOKUP 수식을 XLOOKUP으로 변환하는 방법

기본 제공 변환기는 없지만 변환 과정은 기계적입니다.

1. VLOOKUP을 찾습니다. Ctrl+F(Mac은 Cmd+F)를 누르고 VLOOKUP을 입력한 다음, 추가 설정이 숨겨져 있으면 옵션을 클릭하고 찾는 위치(Look in)를 수식(Formulas)으로 설정하고 모두 찾기를 클릭합니다.
2. 첫 번째 인수(찾을 값)는 그대로 둡니다.
3. 표를 그 표의 첫 번째 열로 바꿔 lookup_array로 사용합니다.
4. 열 번호를 해당하는 열로 바꿔 return_array로 사용합니다. A열에서 시작하는 표의 5번째 열은 E열입니다.
5. FALSE를 삭제합니다. 원래 수식이 TRUE로 끝났거나 마지막 인수가 없었다면, "다음으로 작은 값" 동작을 유지하도록 match_mode -1을 추가합니다.

예를 들면 다음과 같습니다.

=VLOOKUP(A2, Products!$A$2:$F$500, 5, FALSE)
=XLOOKUP(A2, Products!$A$2:$A$500, Products!$E$2:$E$500)

시트 전체를 변환하기 전에 몇 가지 결과를 기존 수식과 비교해 확인하세요. 정렬되지 않은 데이터에 쓴 근사 일치 VLOOKUP은 처음부터 잘못된 값을 반환했을 수 있으므로, XLOOKUP의 결과가 달라지는 것이 정당할 수 있습니다.

XLOOKUP 파일을 Excel 2016 또는 2019에서 열면 생기는 일

통합 문서는 열리지만 이 버전들은 해당 함수를 알지 못합니다. 수식 입력줄에는 =_xlfn.XLOOKUP(...)으로 표시됩니다. 처음에는 셀에 마지막으로 저장된 결과가 계속 표시될 수 있지만, 수식이 다시 계산되는 순간 #NAME?이 반환됩니다.

이전 버전을 사용하는 사람들과 파일을 공유한다면 다음과 같이 하세요.

• 대신 INDEX/MATCH 또는 VLOOKUP을 사용하거나,
• 결과를 값으로 붙여넣은 사본을 보냅니다(복사 → 선택하여 붙여넣기 → 값).

FILTER, UNIQUE, XMATCH 같은 다른 최신 함수에도 같은 내용이 적용됩니다.

Google Sheets의 XLOOKUP

Google Sheets에도 같은 인수 순서의 XLOOKUP이 있습니다: =XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode]). 이 글의 수식은 그대로 작동하므로, Excel과 Sheets 사이를 오가는 파일에는 XLOOKUP이 안전한 선택입니다.

조회 결과가 #N/A이거나 잘못된 값이 나오는 이유

• 불필요한 공백. 끝에 공백이 있는 "P-104 "는 "P-104"와 일치하지 않습니다. TRIM으로 데이터를 정리하세요.
• 텍스트로 저장된 숫자. 텍스트로 입력된 ID는 숫자로 저장된 같은 ID와 일치하지 않습니다. 셀 모서리의 녹색 삼각형을 찾아보고 열을 한 가지 형식으로 통일하세요.
• VLOOKUP에서 FALSE를 빠뜨림. #N/A 대신 그럴듯하지만 잘못된 답이 나옵니다.
• 수식을 복사할 때 이동한 범위. 조회 표에는 $ 절대 참조를 사용하세요.
• VLOOKUP의 #REF!. 열 번호가 표의 열 수보다 큽니다.
• XLOOKUP의 #VALUE!. 조회 범위와 반환 범위의 크기가 다릅니다(예: A2:A6과 D2:D60).
• #NAME? 함수 이름의 철자가 틀렸거나, XLOOKUP이 없는 버전에서 파일을 열었습니다.

자주 묻는 질문

XLOOKUP이 VLOOKUP보다 나은가요? 네, 거의 모든 경우에 그렇습니다. 기본이 정확히 일치이고, 어느 방향으로든 검색하며, 열을 옮겨도 깨지지 않고, 감싸는 함수 없이 값이 없는 경우도 처리합니다. VLOOKUP의 유일한 실질적 장점은 모든 Excel 버전에서 작동한다는 것입니다.

XLOOKUP이 VLOOKUP보다 빠른가요? 일상적인 통합 문서에서는 차이를 느끼지 못합니다. 아주 큰 정렬된 목록에서는 XLOOKUP의 이진 검색 모드(search_mode 2 또는 -2)로 속도를 높일 수 있지만, 데이터가 정렬되어 있지 않으면 잘못된 결과가 반환됩니다.

내 컴퓨터에서 XLOOKUP이 #NAME?으로 표시되는 이유는 무엇인가요? 사용 중인 Excel 버전에 XLOOKUP이 포함되어 있지 않기 때문입니다. XLOOKUP은 Microsoft 365, Excel 2021 이상, 웹용 Excel에 있으며 Excel 2019와 2016에는 없습니다. 대신 INDEX/MATCH를 사용하세요.

XLOOKUP이 HLOOKUP도 대체하나요? 네. 조회 범위와 반환 범위가 열이 아닌 행이라면 XLOOKUP이 같은 구문으로 가로로 검색합니다.

XLOOKUP이 첫 번째 항목이 아니라 모든 일치 항목을 반환할 수 있나요? 아니요, 일치 항목 하나(또는 열로 이루어진 한 행)를 반환합니다. 일치하는 모든 행을 가져오려면 FILTER를 사용하세요. 예: =FILTER(A2:D6, C2:C6="Office").

그래도 VLOOKUP을 배워야 하나요? 이전 통합 문서와 템플릿에서 마주치게 되므로 알아볼 수 있을 정도로는 알아 둘 만합니다. 새 수식에는 XLOOKUP을 배우고, 이전 Excel에서 열려야 하는 파일을 위해 INDEX/MATCH도 기억해 두세요.

AI가 조회 수식을 대신 작성하게 하기

조회 수식은 FALSE를 빠뜨리거나 범위가 어긋나는 것 같은 사소한 문제로 조용한 오류가 가장 많이 생기는 곳입니다. CoreGPT for Excel을 사용하면 "G2의 제품 ID에 해당하는 가격 찾아 줘", "각 행이 목표보다 높은지 낮은지 표시해 줘"처럼 필요한 내용을 설명하기만 하면, 짧은 설명과 함께 시트에 수식을 작성해 줍니다. 무료로 시작할 수 있으며 Windows, Mac, 웹용 Excel에서 사용할 수 있습니다.

직접 사용해 보세요: CoreGPT for Excel

함께 읽으면 좋은 글:

• 엑셀 중복값 제거 방법 5가지 (데이터를 지우는 방법과 지우지 않는 방법)
• Microsoft 365 Excel 꿀팁: 매주 시간을 아껴 주는 업무 단축 노하우
• 엑셀에서 ChatGPT 사용하는 방법: 완벽 가이드