How to Use XLOOKUP in Excel

Using the Excel spreadsheet could be consuming at times especially when you have little knowledge in the technicalities. But in all honesty, the Excel spreadsheet happens to be a magic ride when you’ve found your way around it. All I have to do is enter a formula, and pretty much anything I'd ever need to do manually is done automatically. Do I intend to merge two sheets with similar data? is there a need to do simple math? Need to combine the information in multiple cells? Don’t fret! Excel can do it. 

If by any chance you encounter a situation where you need to manually enter your data, I bet you're just a formula away. One of which we shall be reviewing in this article.

The XLOOKUP as a formula has been recently celebrated as a modification to the VLOOKUP and HLOOKUP, where the VLOOKUP on the popular ground has enjoyed acceptance and a great deal of use since the Macintosh series in 1985.

OK! Let's get to it.

Well, to get us started on this, you ought to have upgraded to the new Office 365, since you need Microsoft 365 subscription version to use XLOOKUP.  

The XLOOKUP gets its name from its ability to search both vertically and horizontally. In its most basic form, XLOOKUP requires only three arguments to perform the most common exact lookup (one less than VLOOKUP).  This is what it looks like in its basic.

XLOOKUP (lookup_value,lookup_array,return_array)

If we are to consider the XLOOKUP in its extended form, it would look like this:

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

Here are the functions to the syntax for your clarity:

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 but 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. 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. The 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 the 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.

Some of the unique features of the XLOOKUP is seen below.

• It can look up vertically (like VLOOKUP) or horizontally (like HLOOKUP)
• You can specify an entry to return if no match is found
• The code to look up can be in any column/row of the table
• It can return a reference to a cell
• 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 ?

Just so we're clear, VLOOKUP never actually looked up. It starts at the top and goes down the column until it finds the first code that matches the value.

The default XLOOKUP

Does the same, but the search_mode argument can instruct XLOOKUP to start at the bottom and lookup. This means you can find the last entry in a list rather than the first entry, amazing right?

In conclusion, the XLOOKUP is a major improvement on VLOOKUP, since it provides more flexibility, considering its robust path in providing solutions for all your lookup requirements. So, I'll suggest you get your application upgraded and start enjoying the seamless newbie to the Excel spreadsheet.

For more information on some unique features on the Excel Spreadsheet, Website development, Digital Marketing, and SEO, you can always trust The Watchtower, the best and award-winning Website Development Company, Dubai.