How to Use XLOOKUP for Horizontal Searches?
Struggling to find values across rows? XLOOKUP can help you search horizontally and return the desired results seamlessly.
Formula Genius can generate a draft from a plain-English prompt. Supported formulas can be checked with sample cases; review the coverage on the result.
The formula
"Use XLOOKUP to search across rows instead of down columns, returning a value from a matching column"
=XLOOKUP(lookup_value, lookup_array, return_array, , , 1)This formula searches for a value in a row and returns a corresponding value from another row based on the match.
Step-by-step breakdown
- lookup_value: The value you want to find in the row.
- lookup_array: The row where you want to search for the lookup_value.
- return_array: The row from which you want to retrieve the corresponding value.
- match_mode: Set to 1 for an exact match or next larger item.
Edge cases & warnings
- If the lookup_value is not found, the formula returns #N/A.
- If the lookup_array or return_array is empty, the formula will also return #N/A.
- Using a non-horizontal range for lookup_array will lead to an error.
Examples
"lookup_value: 'Product B', lookup_array: A1:E1, return_array: A2:E2"
'$20'"lookup_value: 'Product D', lookup_array: A1:E1, return_array: A2:E2"
'#N/A'Frequently asked questions
Can XLOOKUP return multiple values?
No, XLOOKUP returns only the first matching value.
What happens if there are duplicate values?
XLOOKUP will return the first match it finds.
Is XLOOKUP available in all Excel versions?
XLOOKUP is available in Excel 365 and Excel 2021.
Can't find what you need?
Describe a formula in plain English. Formula Genius drafts and explains it, and can check supported formulas on sample cases.