The article below explains two ways to solve this problem.
The first approach, based on the XLOOKUP function is the simplest.
The keywords and categories are completely arbitrary and can be customized to suit the situation.
Background study
This is a more advanced lookup formula.
error if the text string is not found.
errors except for the fourth value, which is 1.
XLOOKUP is configured with thelookup_valueset to TRUE.
As the formula is copied down, the same operation is performed for each expense listed in column B.
The operation of this formula is similar to the XLOOKUP formula above.
error comes up whenever SEARCH can’t find the text.
since “dr” appears inside these words.
This happens because SEARCH looks for a substring and has no concept of words.
For a quick hack, it’s possible for you to add space around the search words (i.e. "
dr “, or “dr “) to avoid catching “dr” in another word.
Then you could search for whole words surrounded by spaces.
For a detailed explanation of how this works,see this example.
it’s possible for you to use INDEX to retrieve individual values, or entire rows and columns.
MATCH supports approximate and exact matching, andwildcards(* ?)
SEARCH Function
The Excel SEARCH function returns the location of one text string inside another.
SEARCH returns the position offind_textinsidewithin_textas a number.
XLOOKUP supports approximate and exact matching, wildcards (* ?)
for partial matches, and lookups in vertical or horizontal ranges….
