Vlookup on partial match

Question

Sample Sheet: https://docs.google.com/spreadsheets/d/16HUbIpN9MfTh5msRl54wMCRddTdKUyST-2XqNsnaPxs/edit?usp=sharing

I need to vlookup on a partial match in the search key. In the attached speed sheet, the search key in column A. If the search key in column A contains the lookup value in column F, the value in column G should be returned in column B.

I would prefer to do it with an array formula because my actual sheet is huge. Your help will be greatly appreciated!

Answer 1

In B2 try

=ArrayFormula(if(len(A2:A), iferror(vlookup(regexextract(A2:A, textjoin("|", 1, F2:F)), F:G, 2, 0)),))

and see if that works?

• Regexextract 'extracts' the part of the search key (column A) that matches the vlookup value (column F).
• Then, the extracted part is used in Vlookup() to retrieve the value (column G).