As a long-time Excel enthusiast, I have always loved using VLOOKUP, one of Excel’s best-known search functions. However, Microsoft’s introduction of XLOOKUP in 2019 changed everything. Once I realized how useful XLOOKUP was, I knew I would never look back.

I will use direct cell references to illustrate my points in this article, as they are clearer than structured references. I also won’t explicitly talk about HLOOKUP because, aside from its direction, it works exactly the same as VLOOKUP.

Syntaxes: XLOOKUP and VLOOKUP

Before explaining why I prefer SEARCHXL has VLOOKUPI’ll show you how they work.

SEARCHXL

XLOOKUP has six arguments:

=XLOOKUP(a,b,c,d,e,f)

Or

• A (required) is the search value,

• b (required) is the lookup table,

• c (required) is the return array,

• d (optional) is the text to return if the search value (A) was not found in the search table (b),

• e (optional) is the match mode, and

• f (optional) is the search mode.

In this example, I want Excel to find the employee number based on the name in cell H1 and return the result in cell H2.

To do this, I will type the following formula in cell H2:

=XLOOKUP(H1,B2:B12,A2:A12,"Invalid name",0,1)

In this case, cell H1 contains the value Excel needs to look for (Mary), B2 to B12 are where this value can be found (employee names), A2 to A12 is where the result comes from matching will be extracted (employee IDs), and “Invalid Name” is what I want Excel to return if the lookup value is not found anywhere in the lookup table.

I opted for default values ​​for arguments e And f because I want an exact match and I want Excel to search from the top of the search table (we’ll get to that later).

VLOOKUP

Here is the VLOOKUP syntax, which has four arguments:

=VLOOKUP(a,b,c,d)

Or

• A (required) is the search value,

• b (required) is the search and return array,

• c (required) is the column index number, and

• d (optional) is match mode.

In this example, I want Excel to search for nationality based on the employee ID number in cell H4 and return the result in cell H5.

To do this, in cell H5, I will type

=VLOOKUP(H4,A2:E12,5,FALSE)

because cell H4 contains the search value (ID 3264), cells A2 to E12 are where Excel should find this value and the corresponding return, the fifth column (the country) is the table in which the result will be found, and I want an exact match (FALSE).

No need to count columns

A key difference between VLOOKUP and XLOOKUP is that the former requires me to specify a column index number where the result will be found, whereas the latter does not. This is because VLOOKUP combines the lookup and return arrays into a single argument, while XLLOOKUP defines them in two separate arguments.

VLOOKUP

Having to specify the column index number in VLOOKUP can cause several problems:

• It’s easy to accidentally miscount columns, especially if your lookup table has hundreds of columns.

• Add or remove columns could affect the accuracy of the column index number.

• The lookup table should be the leftmost column and the return table should be on the right. This limits the versatility of VLOOKUP.

SEARCHXL

On the other hand, XLOOKUP contains the lookup table as a separate argument, which means you can enjoy the following benefits:

• There is no counting! Simply use your mouse to select the return array when you get to this part of your formula. This saves time and improves accuracy.

• Because the returned table is within a specified cell range, removing or adding columns to your worksheet will not affect your XLOOKUP formula.

• The return board can be on either side of the lookup board, which means that XLOOKUP is more versatile than VLOOKUP.

More fuzzy match options

VLOOKUP and XLOOKUP can return an exact match (the exact matching value in a row) and an fuzzy match (a close matching value).

VLOOKUP

Specifically, the VLOOKUP fuzzy match (indicated by TRUE in the formula) searches the lookup table until it finds a value greater than the lookup value. It then returns the value one line up from there.

Consider Student D’s score of 65 in the example below. VLOOKUP takes the lookup value of 65, examines the lookup table, finds the first value greater than the lookup value (in this case, 70), and then returns the grade for the row above (grade C).

This has two disadvantages. First, the lookup table should be listed in ascending order. Second, I need to add a FAIL row to the lookup table, because I don’t have the option in VLOOKUP to specify an argument that doesn’t match.

SEARCHXL

On the other hand, XLOOKUP offers three alternatives to VLOOKUP’s one-dimensional fuzzy matching:

• -1: Returns the next smallest value in the lookup array if there is no exact match.

• 1: Returns the next largest value in the lookup table if there is no exact match.

• 2: This uses wildcards to allow more flexible searches.

Again, let’s take Student D as an example. With a score of 65, Excel will see that the next smallest value in the lookup table is 60 and the second largest value is 70. Since the student has not yet reached the B grade threshold (70 ), I need Excel to take the next smallest value in the lookup table (60) to return grade C from the return table. So I’m going to type -1 as a match option in the formula.

This means that the lookup table does not need to be in order. Excel goes through the entire lookup table to find the closest higher or lower values ​​if there is not an exact match. I can also omit the FAIL row from my lookup table, because if a student’s score does not match any grade, I can use the fourth argument of the XLOOKUP syntax to return the word FAIL.

More search modes (directions)

While VLOOKUP searches from first to last, returning the first matching value, XLOOKUP offers four search options.

VLOOKUP

In most search scenarios, searching the search table from first to last will return the results you need. For example, if you have a directory of phone numbers and people’s names, using VLOOKUP to find the phone number based on the name you entered will work just fine, because that person’s name probably won’t appear only once.

SEARCHXL

However, XLOOKUP allows you to choose the direction of the search:

• 2: Binary search (with the search table in ascending order)

• -2 Binary search (with search table in descending order)

The advantage of a last-to-first search is that you can find the most recent occurrence of a value in a lookup table listed in date order. To achieve this with VLOOKUP, you will first need to reverse the order of the data.

Set error output

A very useful feature of XLOOKUP is the “if not found” argument, which VLOOKUP does not have.

VLOOKUP

If a value is not found in an exact match VLOOKUP formula, Excel returns the dreaded #N/A error message. To solve this problem, I always integrated VLOOKUP functions into the IFERROR function so I can set the output if VLOOKUP does not find a match.

=IFERROR(VLOOKUP(B6,$E$2:$F$8,2,TRUE)," ")

While this is a reasonable solution, it makes writing formulas much more complex and can hide issues that could affect the accuracy of your data analysis.

SEARCHXL

Since XLOOKUP comes out of the box with an “if not found” argument, you can define what happens if the value doesn’t appear in the search, saving you from having to embed the formula in IFERROR.

Return a flipped array

One of the most stubborn properties of VLOOKUP is that it can only return a single match, whereas XLOOKUP can return a range.

SEARCHXL

In this example, typing

=XLOOKUP(I1,A2:A7,B2:F7)

searches for the value in cell I1 (in this case, Quizpicable Me), searches for that value in cells A2 through A7, and returns all matching values ​​as a reversed painting.

VLOOKUP

If I were to try to reproduce this using VLOOKUP, I would type

=VLOOKUP(I1,A2:F7,2:6)

but this returns a #REF! error because the third argument (column index number) can only be a single digit, not a range. This means that XLOOKUP is much more adaptable, as it can return either a single value or a range, depending on the parameters you add to the formula.

Since versions before 2019 of Excel do not support XLOOKUP, do not remove VLOOKUP and HLOOKUP completely! There may be cases where you need to use them, such as if you’re sending a spreadsheet to someone who hasn’t done so yet. upgraded their Office package for several years.