If no matching colors are found, the formula should return the value “na”.
Both methods are explained below.
It returns TRUE if the value is numeric, and FALSE if not.
TheSEARCH functionis used to find the starting position of a specific substring within a string.
The syntax is SEARCH(find_text, within_text, [start_num]).
If the substring (find_text) is found, SEARCH returns the starting position as a number.
If not, it results in an error.
TheXLOOKUP functionis a modern upgrade to the older VLOOKUP function.
If a color is found, SEARCH returns the starting position as a number.
If a color is not found, SEARCH returns an error.
These numbers indicate the numeric positions of the colors “white” and “blue” in cell B5.
errors indicate colors in E5:E11 that were not found.
This value can be omitted or customized as desired.
First match in sentence, or first match in list?
These are two distinctly different operations.
The formula above returns the first match found in the color list.
However, instead of using the ISNUMBER function as an error handler, it uses theIFERROR function.
IFERROR is set to catch errors from SEARCH and remap them to a zero (0) value.
This formula works in much the same way as the XLOOKUP version above.
Thematch_typeargument is set to zero (0) to force an exact match.
AGGREGATE works well here because it has an option to ignore errors.
The result from AGGREGATE is 5, which is returned to MATCH as thelookup_value.
XLOOKUP supports approximate and exact matching, wildcards (* ?)
SEARCH Function
The Excel SEARCH function returns the location of one text string inside another.
SEARCH returns the position offind_textinsidewithin_textas a number.
you’re free to use INDEX to retrieve individual values, or entire rows and columns.
MATCH supports approximate and exact matching, andwildcards(* ?)
