XLOOKUP Horizontal Lookup

Learn to perform a horizontal lookup using XLOOKUP. With XLOOKUP, you no longer have to use the very limited HLOOKUP function. In the example below, we will perform a lookup with an Exact match and an Approximate Match.

Let’s take a look at the following example. Here, we are using a horizontal lookup to return the batting average of a particular player.

XLOOKUP Horizontal Lookup
XLOOKUP Horizontal Lookup
=XLOOKUP(value,range1,range2) // exact match

=XLOOKUP(value,range1,range2,,-1) // approximate match

As a reminder, an Exact match is a returned result that matches 100% the value being requested. For example, if you want to find “Bob,” Excel will find “Bob.”

For an Approximate match, Excel will find a close, but not Exact result. In the example above, if a name is missing a letter, “Joh, ” Excel will still match “John.” In the formula, we add “-1.”

Click here for a complete understanding of the XLOOKUP Function.

Leave a Reply

XLOOKUP Function in Excel

XLOOKUP replaces VLOOKUP AND HLOOKUP permanently! Speed up your spreadsheet by changing your default lookup function. Add a 4th and 5th argument!

Read More »

PMT Function

The PMT function in Excel calculates the payment for a loan that has constant payments and a constant interest rate.

Read More »
Scroll to Top