Hello all and welcome back to the Excel Tip of the Week! This week, we have a General User post in which we are looking at the various lookup functions offered by Excel, and making a comprehensive comparison between the features and limitations of each of them.
We’ll be covering the gist of them, but if you want a bit of background reading on the functions covered here, check them out in our archive as follows:
What's in a lookup?
Let’s review the basics with a look at what a lookup is and what it’s for.
Here we have a master data source (on the left), and then on the right we have a new location where we want to import information from that data source. The two are connected via some kind of unique label or identifier. The data we want to pull could be numeric (such as the 2019 balances), or it could be non-numeric (such as the initials of the staff member responsible for that area). All lookup functions share these qualities:
- They work with either numbers or text
- They rely on a unique identifier, and will not work if identifiers are repeated
- If searching for an identifier that is not present, they will either return an error or another nearby value depending on the circumstances and how the functions are written
As well as the three above functions, we’ll also be examining LOOKUP. This is an old Lotus 1-2-3 function that’s still usable in Excel for backwards compatibility reasons, but as we will see it is generally not a great choice.
Handling of unsorted data
If your data in unsorted, how will the different functions handle it?
LOOKUP – will not work
VLOOKUP – will work only if the FALSE option is specified
INDEX MATCH – will work only if the 0 option is specified
XLOOKUP – works by default
This is the single big strike against LOOKUP – it only works if the unique identifiers are sorted into order; otherwise it will return incorrect results. VLOOKUP and INDEX MATCH can both handle this situation, but annoyingly their default behaviour is to assume the data is sorted and find an approximate match. XLOOKUP automatically looks everywhere for your result.
Ease of writing
How easy are these functions to input?
LOOKUP – Easy; can directly point at label and value columns
VLOOKUP – Hard; have to select entire data range and count which output column is desired
INDEX MATCH – Medium; have to embed one function within another
XLOOKUP – Easy; can directly point at label and value columns
VLOOKUP suffers the most here, because not only does the entire data range have to be selected, you also have to manually count across to identify which column you want the function to return. INDEX MATCH is also a bit trickier as it requires nesting a MATCH function inside an INDEX function.
Handling of labels not on the left
If your data is not laid out with the labels on the left, how does that affect things?
LOOKUP – Works normally
VLOOKUP – Does not work
INDEX MATCH – Works normally
XLOOKUP – Works normally
Because of the “table array” input in VLOOKUP, it can only handle data where the labels are the leftmost column.
Handling of insertion / deletion of columns
LOOKUP – Unaffected
VLOOKUP – Breaks
INDEX MATCH – Unaffected
XLOOKUP – Unaffected
In another strike against VLOOKUP, because the output column is manually identified through the “column index no”, if columns are inserted or deleted from the data range later on, the VLOOKUP will break.
Handling of horizontal lookups
LOOKUP – Works
VLOOKUP – Has to be replaced with HLOOKUP
INDEX MATCH – Works
XLOOKUP – Works
All four of these families can handle a horizontal lookup – that is, searching along a row for an identifier – although the vertical VLOOKUP has to be supplanted with its sister function HLOOKUP.
Returning multiple values
If we want to use one identifier to return several values, how easy is it to do that?
LOOKUP – Medium – have to set up $-references carefully
VLOOKUP – Hard – have to increment column index number manually or with a COLUMN function
INDEX MATCH – Medium – have to set up $-references carefully
XLOOKUP – Easy – Can return a spilled range of values
VLOOKUP struggles once more, as the need for a direct column index number means that returning more than one value will involve some added complication. INDEX MATCH and LOOKUP can both be copied rightwards with only minor alteration, and XLOOKUP, being a dynamic array function, can directly return multiple values by selecting a wider array for the “return array” input.
Searching for multiple input values
LOOKUP – Simple copying
VLOOKUP – Simple copying
INDEX MATCH – Simple copying
XLOOKUP – One function
Similarly, all the older functions can be used to look for multiple different identifiers by means of a simple copy and paste (with appropriate $-references); however XLOOKUP can also look up an array of inputs with a simple formula. Note that unfortunately you can’t combine this with the above.
If no match is found, how is this handled?
LOOKUP – Finds another value based on proximity or returns #N/A!
VLOOKUP – Returns #N/A!
INDEX MATCH – Returns #N/A!
XLOOKUP – Can be customised to show any desired value
Generally the older lookup functions will return a “not applicable” error if they cannot find an exact match; the IFERROR function is often used to replace this with a custom error message or other function. XLOOKUP has a “value if not found” input built directly into the function.
Which versions of Excel support which functions?
LOOKUP – Lotus 1-2-3 and all Excel versions
VLOOKUP – All Excel versions
INDEX MATCH – All Excel versions
XLOOKUP – Excel 365 only
While easily the best function for lookups, XLOOKUP is only available for Microsoft 365 or Excel Online users. Those with Excel 2019 or earlier will not have access.
If I had to summarise all the above into a single recommendation, it would be this: if you and your collaborators all have access to it, use XLOOKUP; otherwise, use INDEX MATCH. And always think about what you’re trying to do is a lookup at all, and if not, consider using something like SUMIFS or a PivotTable instead.
You can check out demonstrations of all four alternatives in the accompanying file.
You may also like
This article is brought to you by the Excel Community where you can find additional extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business.