Excel tips: how XLOOKUP delivers better functionality than VLOOKUP (updated for 2023)

Updated 7 February 2023

VLOOKUP has been one of Excel’s most popular functions, especially among accountants.

However, Microsoft created VLOOKUP’s replacement XLOOKUP, which removes the limitations and adds extra functionality.

This new function is in the same update release as  dynamic arrays. And you will need Microsoft 365 (until recently known as Office 365) subscription version to use XLOOKUP.

Just like VLOOKUP, XLOOKUP allows you to look up a code in a table and return entries on the same row as the code.

XLOOKUP features

• It can look up vertically (like VLOOKUP) or horizontally (like HLOOKUP)
• The code to look up can be in any column/row of the table
• You can specify an entry to return if no match is found
• It can search from the top down (or left to right) or the bottom up (or right to left)
• You can use wildcard characters such as * and ?
• It can return a reference to a cell 

Even though XLOOKUP works horizontally, most lookups are vertical. This article ignores horizontal lookups. In the syntax below, “array” means “a range”.

Syntax

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

lookup_value – the value to look up, usually a cell reference.

lookup_array – this is the range to look in. This is not a table reference; it is a single column, usually with a fixed reference (both $ signs). The first entry that matches the lookup_value is used. (Note that the search_mode below can change the direction of the search.)

return_array – this is the range to extract from. It can be a single column or multiple columns. If multiple columns are selected, the formula will “spill” across as per dynamic array entries covered in previous articles. If you refer to the range of the whole table, then the whole row from the table is returned.

if_not_found – optional – what to display if the entry is not found. This can be another function, like the IF function. Text needs to be enclosed in quotation marks. If omitted, the N/A error is returned for entries that can’t be found.

match_mode – optional – the type of match to perform. 0 = exact match (default). -1 = exact match or next-smallest. 1 = exact match or next-largest. 2 = wildcard match. The default, if omitted, is the exact match option (0). The range doesn’t need to be sorted for the exact match option (0). The lookup_array needs to be sorted in ascending order for the next-smallest option (1), and descending order for next-largest option (-1). The wildcard match option (2) means you can use *, ? and ~ characters in lookups.

search_mode – optional – the type and direction of the search. 1 = search from top down (default). -1 = search from bottom up. 2 = binary search ascending order. -2 = binary search descending order. The ascending and descending options require that the lookup_array be sorted accordingly. Defaults to the search from top down (1) if omitted.

VLOOKUP could only look up a code in the left-most column of the specified table. XLOOKUP has no limitation on the column to search in. Figure 1 has the table we will extract from. 

We want to extract the Status based on the Project Code as per Figure 2. That was difficult with VLOOKUP, because it couldn’t look up to the left of the code, but this is easy with XLOOKUP.

The formula in cell H2 is

=XLOOKUP(G2,$B$2:$B$10,$A$2:$A$10)

This has been copied down to cell H3. Note: the first three arguments in XLOOKUP are required, and the last three are optional. In this formula, we are looking up the entry in cell G2 within the range B2:B10 and returning the corresponding row from A2:A10. The ranges used are fixed ($ signs added) to enable copying.

Handling missing entries

The entry to look up is typically in a cell. If that cell is blank, it can cause an #N/A error. We can handle that with the if_not_found argument in XLOOKUP, or the IF function. If cell G2 is blank, the above formula returns the #N/A error as per Figure 3.

Here are two different formulas for cell H2 to avoid the #N/A error. Both formulas display a blank cell if cell G2 is blank. Both display the word Error if a non-blank entry isn’t found.

=IF(G2="","",XLOOKUP(G2,$B$2:$B$10,$A$2:$A$10,"Error"))
=XLOOKUP(G2,$B$2:$B$10,$A$2:$A$10,IF(G2="","","Error"))

Things are looking up

Just to be clear, VLOOKUP never actually looked up. It always looked down. It started at the top and went down the column until it found the first code that matched the value.

The default XLOOKUP does the same, but the search_mode argument can instruct XLOOKUP to start at the bottom and look up. This means you can find the last entry in a list rather than the first entry. In Figure 4 I have used the search_mode -1 to look up the last Project Code for each Status.

The formula in cell K2, which has been copied down, is

=XLOOKUP(J2,$A$2:$A$10,$B$2:$B$10,,,-1)

The -1 on the end instructs XLOOKUP to start at the bottom of the range and look up.

XLOOKUP references

Most Excel functions can’t return a reference. To explain and demonstrate what returning a reference means, let’s look at a simple example.

Let’s say we want to add up the values of all the projects from the first project in row 2 down to a project code that you specify in a cell. See Figure 5. The formula in cell N2 is

=SUM(E2:XLOOKUP(M2,B2:B10,E2:E10))

Because the XLOOKUP is on the right side of the colon in a range reference, it provides the ending cell reference for the SUM function to use. In this case, it returns a reference to cell E7.

You could use an XLOOKUP for both the start and end cell references to provide a totally flexible range to SUM.

Multiple columns (all columns)

If you refer to the range of the whole table in the return_array argument, the whole row will be returned, and cells will spill to the right. Spilling is a new feature associated with dynamic arrays. Figure 6 has an example of this. The formula is entered in cell Q2, and it spills across to the other cells. The formula spills across five columns, because the return_array is five columns wide A2:E10.

Wildcards

The use of the wildcard characters will be included in the companion video and the website version of this article. Figure 7 demonstrates lookups using wildcards with a different table.

Better solution

XLOOKUP is a major improvement on VLOOKUP. It provides more flexibility and is more robust in providing solutions for all your lookup requirements.

The companion video and Excel file will go into more detail to demonstrate these techniques.

Neale Blackwood CPA runs A4 Accounting, providing Excel training, webinars and consulting services to organisations around Australia. Questions can be sent to [email protected]