Explanation
The general goal is to return the first text value in a range.
This problem can be solved using the XLOOKUP function.
In older versions of Excel, you might use the VLOOKUP function or an INDEX and MATCH formula.
It is also possible to solve this problem with a more modern array formula based on the ISTEXT function.
It’s not obvious from the description, but the asterisk (*) wildcard will only matchtext characters.
It will ignore empty cells, numbers, and errors.
Like XLOOKUP, VLOOKUP supports wildcards.
The generic syntax for VLOOKUP looks like this:
For more details, seeHow to use the VLOOKUP function.
Note that VLOOKUP is limited tovertical ranges only.
Alternatively, you might use an INDEX and MATCH formula, as explained below.
The location is then provided to INDEX, which returns the final result.
For more details on INDEX with MATCH, see:How to use INDEX and MATCH.
We then configure XLOOKUP to search this array for the first TRUE value.
For a more complete explanation,see this example.
XLOOKUP supports approximate and exact matching, wildcards (* ?)
you’ve got the option to use INDEX to retrieve individual values, or entire rows and columns.
MATCH supports approximate and exact matching, andwildcards(* ?)
