Tired of arguing the relative merits of VLOOKUP or INDEX/MATCH?
Well, now there’s a new one to add. XLOOKUP is here to be a quicker, smarter VLOOKUP and XMATCH overhauls MATCH.
Read on for details!
XLOOKUP is billed as a replacement for both VLOOKUP and HLOOKUP.
It will take some getting used to, but should be second nature in no time.
- Instead of selecting the whole range (“table”) that you want to look up from and specifying the row or column number to return, you select only the input and output columns (if replacing VLOOKUP) or rows (if replacing HLOOKUP).
- There is no switch or parameter to specify whether you want to replace VLOOKUP or HLOOKUP; it determines this based on the shape of the input ranges instead.
- You can also stop specifying “false” to get an “exact match”; it now defaults to this, so you only need to flag if you don’t want an exact match.
- If not wanting an exact match, you can now specify the previous OR the next “bracket”.
- You can now also lookup in a negative direction, i.e. to the left for columns or upwards for rows. (For some common use cases, we think this has the potential to save a lot of calculation power!).
- You can also search in reverse order, i.e. find the last instance of the lookup value.
- After your formula is working and forgotten, it will not be affected if you insert or delete columns in between the lookup and return values.
- XLOOKUP is much faster than VLOOKUP! It doesn’t have the same volatility characteristics. This is probably due to the way it works behind the scenes (it’s virtually index/match in disguise, but sssh!). This calculation speed increase is the main reason that XLOOKUP will be a game changer and a favourite in years to come.
- Formulaic row or column numbers to return. These are a parameter in VLOOKUP (for column number) or HLOOKUP (for row number) and can be formula-driven. Due to the way XLOOKUP works, you can’t use XLOOKUP this way, and there are good reasons for this (mainly the calculation speed increase. Don’t worry though, if you need to keep the return column or row number as a formula then you can still use VLOOKUP or HLOOKUP instead anyway!
XMATCH adds more flexibility and ease of use to the existing MATCH function.
- Exact match is now by default
- Can now look up in reverse direction
- With XMATCH, you can more easily specify what to return (or calculate) if it can’t find a match. (With MATCH, you had to trap for a #N/A error result).
If you need a formula-based return column number in VLOOKUP, you will still need to use VLOOKUP. For everything else, you can start using XLOOKUP where you would normally use VLOOKUP… which is seldom, because you mostly use INDEX/MATCH instead, right? 😉
We think XLOOKUP will quickly become one of the most used functions in Excel. Don’t be surprised if it ends up going top 3!
XMATCH will probably cause INDEX/XMATCH to become the de-facto standard for most things you would currently do with INDEX/MATCH.
Learn more about XLOOKUP and XMATCH at Announcing XLOOKUP.