How to Use XLOOKUP for Horizontal Searches?
XLOOKUP Across Rows is a Excel function that searches for a value in a row and returns a corresponding value from another row based on the match.. Formula Genius generates and validates this formula automatically from a plain-English prompt.
Struggling to find values across rows? XLOOKUP can help you search horizontally and return the desired results seamlessly.
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 any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.