You’ve heard of VLOOKUP and possibly it’s much-under-used sibling HLOOKUP. Now meet the new(ish) kid on the block, XLOOKUP. I say newish because it was actually released in the early 2020.
Although branded as a replacement for V-and-H-LOOKUP (or to use modern parlance, a VLOOKUP Killer), XLOOKUP is only available to Microsoft 365 subscribers which means that V-and-H-LOOKUP will probably be around for a long time to come.
So even if you are a Microsoft 365 subscriber, should you use XLOOKUP or stick with the “Devil you know”? Here’s 5 reasons why you should consider XLOOKUP.
XLOOKUP Can Return a Value From The Left or Right
Whereas VLOOKUP can only return a value from a column to the right of the “lookup column”, XLOOKUP can return a value from a column to the left or right.
In the example below, I can type an Office location in F1 and use XLOOKUP in F2 and F3 to retrieve the name of the manager the name of the country.
VLOOKUP would require the Office column to be the left-most column of the table and Manager and Country to be to the right of it
The basic syntax of XLOOKUP is
=XLOOKUP(lookup-value, lookup-range, result-range)
XLOOKUP Won’t Break When a Column is Inserted
In the example below, a column has been added to the table (Years of Service in column B)
VLOOKUP relies on a “column index number” where each column within the lookup table is assigned a number. If Country was column index 3 and a new column is added to the table to the left of Country, it pushes Country to the right and it becomes column index 4, which will break the VLOOKUP
However, because XLOOKUP doesn’t use a column index number, adding additional columns within the table won’t cause the formula to break.
XLOOKUP Lets You Define an If-Not-Found Parameter
In the example below, both XLOOKP and VLOOKUP would generate #N/A because the value being looked up (Munich) cannot be found in column B.
To fix this using VLOOKUP would require the VLOOKUP formula to be wrapped in an IFERROR. However with XLOOKUP, you can define an if-not-found value as a parameter…
F2: =XLOOKUP(F1,B2:B5,A2:A5,”Invalid Office Name”)
XLOOKUP Defaults to Exact Match (False)
In my opinion and experience, the majority of lookups are exact match lookups. In other words, the item being looked up must match one of the items in the lookup array, which makes me wonder why in the absence of the 4th parameter, VLOOKUP defaults to approximate match (TRUE).
I personally always specify the “match-type” parameter but if you choose not to, XLOOKUP defaults to exact match.
XLOOKUP Can Find the Next Highest Value
The screenshot below is from a spreadsheet used by a company that sells jars of coffee. The jars are sent to the customers in containers. Each container can hold 100 jars, so for example, an order of 200 jars would require 2 containers
But what if a customer ordered 220 jars? That would require 3 containers – 2 full containers (100 jars in each) and one container of 20 jars.
When approximate match is defined as the 4th parameter, VLOOKUP can only find the “next lowest” value, which means in this case, the packing team, who rely on the spreadsheet, would need to fit 220 jars into 2 containers.
Cue a disgruntled customer who has either received broken jars (because the packing team tried to squash 220 jars into 2 containers) or has not received their full order (“sorry we only have 2 containers so we are sending you 200 jars”)
Although there is a workaround involving INDEX/MATCH, it’s much easier with XLOOKUP where you simply specify “Exact match or Next Larger Item” as a parameter. (Exact match or Next Larger Item is assigned a value of 1)
E3: =XLOOKUP(E1,A2:A6,B2:B6,”Invalid Value”,1)