Google sheets lookup with substring on range

Google sheets lookup with substring on range

=TEXTJOIN(,,1,QUERY(C$2:C$4,select C where &A2& contains C))
  • QUERY to find the substring match
  • TEXTJOIN to join matched substring,if more than one is found.

Put the following formula in B2, which then needs to be copied down (alas, I did not find a method of applying ARRAYFORMULA to the second range in FILTER).

=filter(C$2:C$4, regexmatch(A2, C$2:C$4))

enter

Google sheets lookup with substring on range

@ttarchalas answer works. If your range contains several matches but only want 1, wrap it with INDEX:

=INDEX(FILTER(C$2:C$4, regexmatch(A2, C$2:C$4)),1)

An alternative is to use the following, which allows for values in the range to be regular expressions:

=REGEXEXTRACT(A2,JOIN(|,C$2:C$4))

enter

Eg see how 3c3 fails to match cb because of the b word boundary whereas c matches it.

Leave a Reply

Your email address will not be published. Required fields are marked *