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