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.
=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
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”)
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.