In older versions of Excel, you’re free to use an alternative formula, as 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 not found, SEARCH returns an #VALUE!
errors indicate colors in E5:E11 that were not found.
Valid numbers become TRUE, and errors become FALSE.
All other colors in the list were not found.
XLOOKUP scans the array starting at the end.
When a match is found, XLOOKUP returns thecorrespondingcolor fromlist(E5:E11).
If no match is found, XLOOKUP returns an #N/A error.
To customize this result, provide a value forif_not_found.
These are two distinctly different operations and therefore need two different formulas.
The formula above returns the last match found inlist(E5:E11).
This is the total number of characters in B5, which is a number that XLOOKUP will never find.
The SEARCH function is used exactly the same way as before.
For more details on XLOOKUP, seeHow to use the XLOOKUP function.
errors indicate other colors not found.
This may seem odd, but it is intentional.
We use 2 as a lookup value to force LOOKUP to scan to theend of the data.
LOOKUP will automatically ignore errors, so the only thing left to match are the 1s.
It will scan through the 1s looking for a 2 that can never be found.
AGGREGATE works well here because it has the option toignore errors.
The result from AGGREGATE is 34, which is returned to MATCH as thelookup_value.
For more details on INDEX with MATCH, seeHow to use the INDEX and MATCH.
XLOOKUP supports approximate and exact matching, wildcards (* ?)
LOOKUP’s default behavior makes it useful for solving certain problems in Excel.
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 could use INDEX to retrieve individual values, or entire rows and columns.
MATCH supports approximate and exact matching, andwildcards(* ?)
Related videos
Basic XLOOKUP example
How to look things up with INDEX and MATCH
