目錄
VLOOKUP vs. XLOOKUP - syntax comparison
1. Where to search (lookup array vs. table array)
2. Where to return a value from (return array vs. column number)
3. How to match (match mode vs. range lookup)
4. How to handle missing values (if not found)
5. How to search (search mode)
Difference between VLOOKUP and XLOOKUP
1. Position of lookup column (left lookup)
2. Return values from multiple columns
3. Search with multiple criteria
4. Finding the last occurrence
5. Column insertions or deletions
6. Size limit for the lookup value
7. Sorting of lookup range
8. Versatility in matching
9. Handling errors
10. Efficiency and speed
11. Ease of use
12. Compatibility with Excel versions
XLOOKUP vs. VLOOKUP - summary table
XLOOKUP vs. HLOOKUP
1. Search in any row
2. Search in both directions
3. Matching types and defaults
Practice workbook for download
首頁 專題 excel Excel Xlookup vs Vlookup:差異和優勢

Excel Xlookup vs Vlookup:差異和優勢

Mar 23, 2025 am 09:52 AM

In this article, we will explore the differences between XLOOKUP, VLOOKUP and HLOOKUP, helping you choose the right function for your data analysis tasks.

If you work with Excel, you probably know how useful the VLOOKUP function is. It allows you to look up a value in a table and return a corresponding value from another column. But did you know that there is a newer and more powerful function called XLOOKUP that can do even more? In this tutorial, we will compare these two functions and see how they differ in syntax, functionality and performance.

VLOOKUP vs. XLOOKUP - syntax comparison

Like the cornerstone of chemistry is the periodic table of elements, the heart of any Excel function is its set of parameters. Let's take a closer look at the arguments of two popular lookup functions to unravel their nuances.

The syntax of the VLOOKUP function looks like this:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Where:

  • lookup_value - the value to search for.
  • table_array - the entire dataset.
  • col_index_num - the number of the column from which to retrieve the value.
  • range_lookup [optional] - specifies whether to find an approximate match (default - TRUE) or exact match (FALSE).

And the XLOOKUP function has a bit more complex structure:

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Where:

  • lookup_value - the value to search for.
  • lookup_array - the range where to search for the lookup value.
  • return_array - the range from which to return a value.
  • if_not_found [optional] - the value to return if no match is found.
  • match_mode [optional] - controls the type of match such as exact (0 - default), exact match or next smaller (-1), exact match or next larger (1), wildcard (2).
  • search_mode [optional] - specifies the search type such as first to last (1 - default), last to first (-1), binary search ascending (2), binary search descending (-2).

For more detailed explanation of the arguments, please see:

  • XLOOKUP function - syntax
  • VLOOKUP function - syntax

As you can see, both functions need at least three parameters to do their job. But XLOOKUP provides more additional options, making it more powerful and versatile than its VLOOKUP counterpart. Let's break down the main syntax differences in how they work.

Excel Xlookup vs Vlookup:差異和優勢

1. Where to search (lookup array vs. table array)

VLOOKUP forces the user to indicate the entire table_array, searching for the lookup value exclusively in the leftmost column. The column to return a value from is determined by the column number in the subsequent argument.

XLOOKUP, in contrast, allows the lookup and return columns to be specified separately. This separation ensures exceptional flexibility - the lookup column can be positioned to the left or right of the return value column.

2. Where to return a value from (return array vs. column number)

VLOOKUP employs a hardcoded index number to denote the return column, creating vulnerability when altering the lookup array structure. Changes like adding or removing columns may easily break your formula.

With XLOOKUP, you supply a range reference for the return array, making it better at handling changes in your source data. Moreover, XLOOKUP can accommodate multiple columns in the return array, a feature requiring two or more different VLOOKUP formulas to achieve.

Excel Xlookup vs Vlookup:差異和優勢

3. How to match (match mode vs. range lookup)

The range_lookup argument in VLOOKUP and match_mode in XLOOKUP determine whether the formula seeks an exact or approximate match. While both functions share similar possibilities, XLOOKUP defaults to an exact match, offering a more intuitive default setting. Additionally, XLOOKUP provides an extra option for an approximate match returning the next larger value, without requiring the lookup array to be sorted, as VLOOKUP does.

Match type VLOOKUP XLOOKUP
Exact FALSE or 0 0 or omitted (default)
Approximate (exact or next smaller) TRUE or 1 (default) Needs the lookup column sorted in ascending order. -1
Approximate (exact or next larger) N/A 1
Wildcard FALSE (exact match) 2

4. How to handle missing values (if not found)

In cases where the lookup value is not found, VLOOKUP can only return the standard #N/A! error, potentially causing you to question the formula's accuracy.

By providing the if_not_found argument, XLOOKUP enables you to return a personalized message, such as "The value you are looking for is not found in the database".

Simply put, while VLOOKUP can only say "I can't find it" with an error, XLOOKUP lets you display something specific if it can't find what you're looking for. For example:

=XLOOKUP(F10, B4:B25, C4:C25, "Oops, nothing is found :(")

Excel Xlookup vs Vlookup:差異和優勢

5. How to search (search mode)

VLOOKUP always starts searching at the top and stops when it finds the first match.

XLOOKUP introduces the search_mode argument, allowing users to do both first-to-last and last-to-first searches. Also, it lets you leverage binary search methods for faster results in extensive datasets.

For example, in the table below, VLOOKUP can only find the supplier for the first order of a given item, while XLOOKUP can do it for both the first and last orders. Just set search_mode to 1 for the first match and -1 for the last match:

=XLOOKUP(J4, F4:F25, G4:G25, , , -1)

Excel Xlookup vs Vlookup:差異和優勢

In summary, XLOOKUP gives more choices and control allowing us to specify different arrays for the lookup and return values, as well as various options for the match mode, search mode, and error handling.

Difference between VLOOKUP and XLOOKUP

The differences in VLOOKUP and XLOOKUP syntax play a crucial role in their performance. Now, let's explore these distinctions in a more detailed manner.

1. Position of lookup column (left lookup)

While VLOOKUP is limited to searching only in the first column in the specified table_array, XLOOKUP can look up values in any column, not just the leftmost one. This means XLOOKUP can easily perform bi-directional lookups without needing any data rearrangement.

As you can notice in the screenshot below, VLOOKUP fails to bring a value from a column (Item) when it's on the right side of the lookup column (Supplier).

=VLOOKUP(E4, A4:B25, 1, FALSE)

XLOOKUP, on the other hand, handles this left-side lookup with ease:

=XLOOKUP(E4, B4:B25, A4:A25)

Excel Xlookup vs Vlookup:差異和優勢

2. Return values from multiple columns

VLOOKUP can only return a single value at a time, limiting its efficiency. On the contrary, XLOOKUP can return values from multiple columns in one go. This means you can use a single XLOOKUP formula instead of multiple VLOOKUPs.

For example, if you want to retrieve all details about a particular item, using VLOOKUP requires four separate formulas. You'd supply col_index_num values ranging from 2 (for Region in column B) to 5 (for Supplier in column E):

To get the region:

=VLOOKUP($B$17, $A$4:$E$13, 2, FALSE)

To get the discount:

=VLOOKUP($B$17, $A$4:$E$13, 3, FALSE)

Due to support for dynamic arrays, XLOOKUP simplifies this process, allowing you to fetch all the data with just one formula:

=XLOOKUP(B17, A4:A13, B4:E13)

Excel Xlookup vs Vlookup:差異和優勢

3. Search with multiple criteria

XLOOKUP shines at searching for values using multiple criteria without needing extra helper columns, unlike VLOOKUP. For practical examples, please check out:

  • XLOOKUP formula with multiple criteria
  • VLOOKUP with multiple criteria

4. Finding the last occurrence

To locate the last occurrence using VLOOKUP, you would need to reverse the order of your source data. However, with the search_mode parameter, XLOOKUP can get the last instance of the lookup value without any extra moves. For practical examples, please see:

  • XLOOKUP to get the last match

5. Column insertions or deletions

VLOOKUP relies on a static index column number, making it vulnerable to issues when updating the data source or making changes to the column order. XLOOKUP handles such modifications easily, ensuring the formula remains intact.

Excel Xlookup vs Vlookup:差異和優勢

6. Size limit for the lookup value

In VLOOKUP, the length of the lookup criteria cannot exceed 255 characters, or you'll get a #VALUE! error. But XLOOKUP imposes no limit on the lookup value’s size. So, if your dataset contains really long strings, XLOOKUP is the solution to avoid errors.

7. Sorting of lookup range

While VLOOKUP demands ascending order sorting for its approximate search, XLOOKUP doesn’t have this limitation, providing adaptability in scenarios where sorting is not possible.

8. Versatility in matching

XLOOKUP stands out for its versatility in matching options offering various choices, such as exact matches, two types of approximate matching (next smaller or next larger), wildcard matches, and even binary searches. In contrast, VLOOKUP has fewer options, limited to exact matches and next smaller in approximate searches.

9. Handling errors

XLOOKUP is better at handling errors or missing values. You can decide what it should show if it can't find the requested value, which VLOOKUP doesn't allow.

10. Efficiency and speed

XLOOKUP is faster and more efficient because it looks only where it needs to (the lookup array and the return array), without wasting time and resources on scanning the entire table like VLOOKUP does.

11. Ease of use

While VLOOKUP has fewer arguments, simplicity in syntax doesn't necessarily equate to ease of use. XLOOKUP’s parameters, such as the lookup array and return array, offer a more intuitive approach compared to VLOOKUP's table array and column index number. This makes XLOOKUP more user-friendly, especially for beginners.

12. Compatibility with Excel versions

This is the only area where VLOOKUP surpasses its counterpart :) While VLOOKUP operates in any version of Excel, XLOOKUP is available only in Microsoft 365, Excel 2021, and Excel for the Web.

These differences highlight XLOOKUP's dynamic and efficient nature, making it a powerful choice over VLOOKUP for various data lookup tasks in Excel.

XLOOKUP vs. VLOOKUP - summary table

The table below outlines the main differences between the XLOOKUP and VLOOKUP functions in Excel. It's a quick guide to help you understand which one might be better for your tasks.

Feature VLOOKUP XLOOKUP
Availability All versions Excel 2021, Excel 365, Excel for the web
Exact match Yes Yes
Approximate matching 1 option (next smaller) 2 options (next smaller or next larger)
Default Approximate match Exact match
Wildcard search Yes Yes
Binary search No Yes
Lookup column needs to be sorted For approximate match For binary search
Limit for a lookup value 255 characters No
Left lookup No Yes
Horizontal lookup (in rows) No Yes
Search in reverse order No Yes
Multiple criteria Complicated, with helper column Easier, without helper column
Return values from multiple columns No Yes
Built-in error handling No Yes
Adding / removing columns may break the formula Yes No

XLOOKUP vs. HLOOKUP

In older versions of Excel, if you wanted to look up data in columns, you'd use VLOOKUP, and for rows, there's HLOOKUP. However, the XLOOKUP function can do both jobs! Let's see how it compares to HLOOKUP.

Excel Xlookup vs Vlookup:差異和優勢

1. Search in any row

HLOOKUP can only search in the top row of the table_array, while XLOOKUP can search in any row.

2. Search in both directions

HLOOKUP can only go from left to right, but XLOOKUP can search in both directions. It's like having a double-sided search tool.

3. Matching types and defaults

HLOOKUP defaults to an approximate match and needs the lookup row to be sorted ascending. On the flip side, XLOOKUP defaults to an exact match, which is what you'd usually need in most situations.

All the other differences we talked about between XLOOKUP and VLOOKUP also apply to HLOOKUP. So, XLOOKUP is like a super function in Excel that can find and return values from any table or range with more flexibility, control and features than both older lookup functions combined.

Summing up: While VLOOKUP has been a reliable tool for many years, XLOOKUP now offers a more robust and user-friendly alternative. Its ability to perform both vertical and horizontal lookups, improved syntax, and enhanced error handling make it a preferred choice for all your lookups. If you have access to Excel 365 or 2021, you should definitely give it a try and see how it can elevate your Excel sheets.

Practice workbook for download

XLOOKUP vs. VLOOKUP - differences with examples (.xlsx file)

以上是Excel Xlookup vs Vlookup:差異和優勢的詳細內容。更多資訊請關注PHP中文網其他相關文章!

本網站聲明
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn

熱AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智慧驅動的應用程序,用於創建逼真的裸體照片

AI Clothes Remover

AI Clothes Remover

用於從照片中去除衣服的線上人工智慧工具。

Undress AI Tool

Undress AI Tool

免費脫衣圖片

Clothoff.io

Clothoff.io

AI脫衣器

Video Face Swap

Video Face Swap

使用我們完全免費的人工智慧換臉工具,輕鬆在任何影片中換臉!

熱工具

記事本++7.3.1

記事本++7.3.1

好用且免費的程式碼編輯器

SublimeText3漢化版

SublimeText3漢化版

中文版,非常好用

禪工作室 13.0.1

禪工作室 13.0.1

強大的PHP整合開發環境

Dreamweaver CS6

Dreamweaver CS6

視覺化網頁開發工具

SublimeText3 Mac版

SublimeText3 Mac版

神級程式碼編輯軟體(SublimeText3)

熱門話題

Java教學
1659
14
CakePHP 教程
1415
52
Laravel 教程
1310
25
PHP教程
1258
29
C# 教程
1232
24
Excel中的中位公式 - 實際示例 Excel中的中位公式 - 實際示例 Apr 11, 2025 pm 12:08 PM

本教程解釋瞭如何使用中位功能計算Excel中數值數據中位數。 中位數是中心趨勢的關鍵度量

如何在Excel中拼寫檢查 如何在Excel中拼寫檢查 Apr 06, 2025 am 09:10 AM

該教程展示了在Excel中進行拼寫檢查的各種方法:手動檢查,VBA宏和使用專用工具。 學習檢查單元格,範圍,工作表和整個工作簿中的拼寫。 雖然Excel不是文字處理器,但它的spel

Excel共享工作簿:如何為多個用戶共享Excel文件 Excel共享工作簿:如何為多個用戶共享Excel文件 Apr 11, 2025 am 11:58 AM

本教程提供了共享Excel工作簿,涵蓋各種方法,訪問控制和衝突解決方案的綜合指南。 現代Excel版本(2010年,2013年,2016年及以後)簡化了協作編輯,消除了M的需求

Google電子表格Countif函數帶有公式示例 Google電子表格Countif函數帶有公式示例 Apr 11, 2025 pm 12:03 PM

Google主張Countif:綜合指南 本指南探討了Google表中的多功能Countif函數,展示了其超出簡單單元格計數的應用程序。 我們將介紹從精確和部分比賽到Han的各種情況

Excel中的絕對值:ABS功能與公式示例 Excel中的絕對值:ABS功能與公式示例 Apr 06, 2025 am 09:12 AM

本教程解釋了絕對價值的概念,並演示了ABS函數的實用Excel應用,以計算數據集中的絕對值。 數字可能是正面的或負數的,但有時只有正值是需要的

Excel:組行自動或手動,崩潰並擴展行 Excel:組行自動或手動,崩潰並擴展行 Apr 08, 2025 am 11:17 AM

本教程演示瞭如何通過對行進行分組來簡化複雜的Excel電子表格,從而使數據易於分析。學會快速隱藏或顯示行組,並將整個輪廓崩潰到特定的級別。 大型的詳細電子表格可以是

如何將Excel轉換為JPG-保存.xls或.xlsx作為圖像文件 如何將Excel轉換為JPG-保存.xls或.xlsx作為圖像文件 Apr 11, 2025 am 11:31 AM

本教程探討了將.xls文件轉換為.jpg映像的各種方法,包括內置的Windows工具和免費的在線轉換器。 需要創建演示文稿,安全共享電子表格數據或設計文檔嗎?轉換喲

Google表圖教程:如何在Google表中創建圖表 Google表圖教程:如何在Google表中創建圖表 Apr 11, 2025 am 09:06 AM

本教程向您展示瞭如何在Google表中創建各種圖表,並為不同的數據方案選擇正確的圖表類型。 您還將學習如何創建3D和gantt圖表,以及如何編輯,複製和刪除圖表。 可視化數據是CRU

See all articles