Published in

masterpenguin

# How to use XLOOKUP in Excel?

XLOOKUP is the replacement to VLOOKUP and HLOOKUP function in 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

# 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.

# 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.

# 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

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.