Is it possible to turn the lookup_value and lookup_array around in =MATCH(lookup_value, lookup_array, [match_type]) from =MATCH("*"&lookup_value&"*", lookup_array, [match_type]) - seen more often - to something that would work like =MATCH(lookup_value, "*"&lookup_array&"*", [match_type]) or =MATCH(MID(lookup_value,*,*), lookup_array, [match_type]) (double asterix since I don't know where the matching word(s) start and end)?
Based on the functional description of thousands of pieces of equipment I'm registering I would like to add one of the approx. 330 types they could be divided into; typicals.
Since the descriptions can slightly vary I try to match the long descriptions to the summaries of these descriptions. These descriptions subsequently equal the typicals: type abbreviation I need to provide each equipment with.
Example:
Equipment description (table 1)
hand operated valve to protect vacuum pumps
hand operated vent valve
hand operated valve to TRV
hand operated drain valve
Partial description (summary; table 2)
drain
vent
hand operated valve
With next to it (in table 2) the three corresponding type abbreviations (typicals)
DRN
VNT
HOV
In the end I would like to have a formula that would match the three characteristics and would result in:
hand operated valve to protect vacuum pumps | HOV
hand operated vent valve | VNT
hand operated valve to TRV | HOV
hand operated drain valve | DRN
Combining INDEX and MATCH would enable me to get the third string value returned from the adjacent column after a match between two identical strings has been made. However the fact that my lookup value is longer than the matching value prevents me from nailing it.
Using VLOOKUP sounded promising as well, but I can't combine two arrays that should mutually match.
How can I get this solved?
Bookmarks