VLOOKUP - Partial match but the partial being in the lookup table (GOOGLE SHEETS)

[ogs1999]

Hello!

I've searched everywhere and have been unable to find an answer to this. In Google Sheets I want to do a VLOOKUP but I want a partial match. All my google searches turn up solutions that make the search key partial using '*' wildcard, however what I need is for the lookup to be partial.

This is simplified worked example (the real example is loads of transactions that contain a shared string e.g. "ebay" but then a load of other characters.

Search keys

• North Dakota
• South Dakota
• New York
• Colorado

VLOOKUP range

• Dakota, Orange
• New, Red
• Col, Blue

I want to be able to do a VLOOKUP which will assign the right colors to the right states so lookups on both North and Sourth Dakota would return "Orange" because they'd match Dakota.

Is it possible?!

Thanks