masterpenguin
Published in

masterpenguin

How to use XLOOKUP in Excel?

XLOOKUP is the replacement to VLOOKUP and HLOOKUP function in Office 365.

Note: Formulas in this article require Office 365

It’s a powerful replacement to one of Excel’s most popular functions. This new function solves some of VLOOKUP’s limitations and can do much more too.

Lets see first how the function works.

The XLOOKUP function searches for an item in a range or table, and then returns the item corresponding to the first match it finds.

The XLOOKUP function requires just three pieces of information. And other three are optional.

Here are few practical examples to explain how XLOOKUP works and how it overcomes some of the limitations of its predecessor VLOOKUP.

No more counting of columns!

In VLOOKUP we are used to counting the number of columns where the resulting data is located, and also entering the 4th argument to specify exact or approximate match.

Using XLOOKUP we just have to specify three arguments. And by default the function matches exact value.

This is indeed neat.

In the XLOOKUP formula in below example:

  • B4 — Value you want to look for
  • B7:B10 — Range where you want to look for B4
  • G7:G10 — Range from where you want the result

and you are done! As simple as 1–2–3

Simple as 1–2–3

No more IFERROR or IFNA!

XLOOKUP handles error through its 4th argument. So no more extra IFERROR or IFNA function required to handle #N/A values.

No more IFERROR or IFNA

On your left? No problem!

With VLOOKUP the solution to lookup on the left is complicated. Other option is to use INDEX & MATCH. But why bother?

XLOOKUP works just fine whether the result column is on left or right.

On your left? No problem

Want approximate match? No need to sort!

Yes VLOOKUP can be used to find approximate match and does pretty good job in doing so.

And XLOOKUP also can do the same using its 5th argument — [match_mode]

Here is VOOKUP & XLOOKUP in action on same set of data

Approximate match? No need to sort

But, where XLOOKUP excels over VLOOKUP is where table is not sorted lowest to highest.

See here XLOOKUP gives correct result in reverse sorted table too.

Approximate match? Works in reverse sort too

Go ahead now and replace all your VLOOKUPs with XLOOKUPs!

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store