What the Lookup Function Chooser does
Excel has several ways to find a value in a table and return a related value. They differ in which versions support them, whether they can return a value to the left of the search column, whether they can match on more than one column, and whether they return one match or all of them. The Lookup Function Chooser asks about each of these points and recommends a function that fits.
Use it when you are building a report that colleagues will open in different Excel versions, when VLOOKUP cannot reach the column you need, or when you need every matching row rather than the first one. The recommendation includes a sample formula and the reason for the choice.
How to use it
- Check your Excel version. XLOOKUP and FILTER need Excel 2021, Microsoft 365 or Excel for the web.
- Answer whether the value you need sits to the left of the column you search.
- Answer whether you match on more than one column, and whether you need one result or all matching rows.
- Answer whether the table is laid out horizontally and whether you need a range match such as a grade band.
- Read the recommendation and the sample formula, then replace the ranges with your own.
- Use absolute references such as $A$2:$A$100 for the lookup ranges before filling the formula down.
Worked examples
Return a value to the left
Input: =INDEX(A2:A100,MATCH(E2,C2:C100,0))
Result: Returns the value from column A on the row where column C equals E2.
Why: VLOOKUP can only return columns to the right of the search column. MATCH finds the row position in C, and INDEX returns the value at that position in A. The 0 requests an exact match.
Every matching row
Input: =FILTER(A2:C100,B2:B100="West","No matches")
Result: Spills every row of A2:C100 where column B is "West". If there are none, it shows "No matches".
Why: Lookup functions stop at the first match. FILTER returns all of them, which suits regional lists or open orders. It requires Excel 2021 or Microsoft 365.
A grade band
Input: =XLOOKUP(85,{0;60;70;80;90},{"F";"D";"C";"B";"A"},,-1)
Result: "B"
Why: Match mode -1 returns an exact match or the next smaller value. 85 is not in the list, so the next smaller value, 80, is used and its grade B is returned.
Common lookup mistakes
- Omitting FALSE in VLOOKUP
- Without FALSE, VLOOKUP uses an approximate match and can return a wrong value from unsorted data without showing an error.
- Relative ranges
- If the table range is not locked with dollar signs, it shifts down as the formula is filled and later rows miss the data.
- Hard-coded column numbers
- A VLOOKUP column index such as 3 points to the wrong column after a column is inserted. INDEX/MATCH and XLOOKUP avoid this.
- XLOOKUP in older files
- Excel 2019 and earlier show #NAME? for XLOOKUP. Choose INDEX/MATCH if the file must work there.
Frequently asked questions
Is XLOOKUP always better than VLOOKUP?
XLOOKUP is more flexible, but it is not available in Excel 2019 or earlier. If anyone opening the file uses an older version, INDEX/MATCH is the safer choice.
How do I match on two columns?
In Microsoft 365, =XLOOKUP(1,(A2:A100=G1)*(B2:B100=G2),C2:C100) multiplies two tests so only rows where both are TRUE return 1. In older versions, a helper column that joins the two keys works with VLOOKUP or MATCH.
Does an approximate match need sorted data?
Yes for VLOOKUP with TRUE and for MATCH with 1: the search column must be sorted ascending. XLOOKUP with match mode -1 or 1 does not require sorting.