Using the XLOOKUP Function in Excel

Mar 11, 2021 | Excel Hints and Tips

Which is better XLOOKUP or VLOOKUP?

You may well have heard of the VLOOKUP function, you may even have heard of the HLOOKUP function but the new kid on the block is the XLOOKUP! 

What is an XLOOKUP? 

The XLOOKUP function is used to look up data in a table.  It is similar to it’s lesser cousins the VLOOKUP and HLOOKUP in that respect but it can replace them both in that it can look up data in both a vertical or horizontal direction. 

It has the other advantages that the item being looked up can be in any column or row of the data not just the first that the other lookup functions require. 

It also has an optional section so if the data cannot be found something else can be done instead.  With the older VLOOKUP and HLOOKUP functions this relies on using and IF function along with ISERROR which can result in a really long function. 

It even has a section which determines whether it looks for an exact match only or if it can’t find it how it finds the nearest option.  Left blank this looks for exact matches only which is what we normally require.  Compare this with VLOOKUP and HLOOKUP where you have to specify if an exact match is what you require.  

Structure  

=XLOOKUP(lookup value, lookup array, return array, if not found, search mode) 

The last 2 parts of this function are optional. 

Lookup value – what we are looking for 

Lookup array – Array or range to search 

Return array – Array or range to return 

If not found – Without this completed #N/A will be returned if a valid match is not returned.  If it is filled in it returns the text inputted 

Search Mode – Specifies how the search will apply 

1 – Performs a search starting at the first item 

-1 – Performs a reverse search starting at the last item 

2 – Performs a search that relies on the data being sorted in ascending order.  If the data is not sorted invalid results will be returned 

-2 – Performs a search that relies on the data being sorted in descending order.  If the data is not sorted invalid results will be returned

Example

In this example the first table contains details of employee numbers and names.  In the second table we are given a list of 3 projects the company is working on along  with the employee number and want to look up the employee name from the first table. 

=XLOOKUP(F2,$A$2:$A$6,$B$2:$B$6,”Invalid employee number”) 

Example of XLOOKUP Function in Excel

In conclusion, although VLOOKUPs and to a lesser extent HLOOKUPs have been a staple of many an Excel spreadsheet in the past it’s likely that XLOOKUPs will become the lookup to go to moving forward.  That is once everyone is aware it now exists!  Microsoft have no intention of suddenly dropping the older lookup functions and on previous form are likely to leave them in as a legacy option for a long time to come. 

Further Reading

If you’ve enjoyed reading about how to use the XLOOKUP Function on Microsoft Excel, you might find the following blog useful:

 

Want to learn more about other Microsoft Word features? Then email lara@laramellortraining.co.uk to discuss how I can help or have a look at the Courses I run.

MEMBERSHIPS

FOLLOW LARA