What is XLOOKUP in Excel?

WATCH THE video.

XLOOKUP is the newest member of the Excel lookup function family and is availabile in the new Office 365 Excel versions. You may already be familiar with VLOOKUP, and other functions such as HLOOKUP, INDEX and MATCH.

XLOOKUP allows us to search for an item in a range and return a matching result, similar to VLOOKUP. However, it offers so much more and is much easier to use.

When using XLOOKUP, you only need to provide 3 basic parameters.

  • The value you are looking for
  • The range where this value should be found
  • The range from which you want the result
  • an optional parameter if the lookup value is not found

XLOOKUP example:

=XLOOKUP(“Jason”, Sales[Sales Person], Sales[Sales])

Returns [Sales] for Jason if the name exists in [Sales Person] column.

If you compare this with VLOOKUP, you'll notice that we no longer need to specify the column index number or true/false to perform the search. Hard coding the column index number caused errors in VLOOKUP when inserting or deleting columns, this problem is fixed with XLOOKUP.

This also means, unlike VLOOKUP, XLOOKUP can actually look anywhere in the data to return the result, unlike VLOOKUP which searches the leftmost column and can only return a matching value to the right. This is a huge benefit and means no more complicated INDEX+MATCH formulas or convoluted VLOOKUPs.

So What Makes XLOOKUP Better?

  • XLOOKUP makes lookups in Excel very straight forward and less prone to errors. You just write =XLOOKUP(what you want to find, the range where you want to search, and the result range) and you get the answer (you can even eradicate those annoying #N/A errors if the value is not found without the use of the additional IFERROR)
  • 4th argument supports value if not found scenario In most business situations, we are forced to use the wrap around formulas IFERROR or IFNA to suppress errors. Not anymore! XLOOKUP offers a 4th argument so that you can add a default output if the value you're looking for is not found.
  • XLOOKUP offers optional arguments to search in special scenarios. You can change the search direction and do wild card searches.

XLOOKUP Syntax

Simple case example:

=XLOOKUP(what you want to look for, lookup range, result range)

=XLOOKUP("Jason", sales[Sales Person], sales[Sales]) 

returns Jason's[Sales] if the name can be found in [Sales Person]

Optional parameters:

By default, you just need 3 arguments for XLOOKUP, as shown above. But you can also use 3 additional arguments to specify how you want the lookup to be done.

NEW 4th Argument for XLOOKUP: IF Not Found - no more IFERROR Yay!

The newly introduced XLOOKUP has an even newer feature. It now supports an additional if not found option. This is the 4th argument.

For example, use:

=XLOOKUP(“Jason”, sales[Sales Person], sales[Sales],”Sales Person Not Found”) to return “Value not found” if the lookup value is unavailable in the search column – sales[Sales Person].

5th Argument for XLOOKUP: Match Type

Use Match Type to tell Excel how you want your MATCH to happen. The default is 0 (exact match) unlike VLOOKUP, but you can also use these other options, shown below.

xlookup 5th paramter - match options

6th Argument for XLOOKUP: Match Direction

Try this if you want to search from bottom to top. The default direction is top down (1).

See you again soon, please comment and share if you found useful!
Anita Lund
www.trainers-direct.com.au  Sydney