Find a substring in a table and return corresponding value

0
votes

I have 2 sheets in Google sheets: Sheet 1

A             B
Kindle .      Book
McDonald .    Restaurant

...

Sheet 2 A B

Kindle Book 1           Book
McDonald Chicago        Restaurant
Amazon Kindle Book 2 .  Book

...

I want a formula that can populate the column B in Sheet 2 using the table in Sheet 1 based on a substring match.

I have tried various versions of vlookup but not able to get it right.

1
google-sheetssubstringgs-vlookup
It is possible for Sheet 1, column A to contain two values that are in the same cell in Sheet 2, column A? If yes, what is the expected output? – cybernetic.nomad
@cybernetic.nomad - No, safe to assume that case would not occur. – pseudocode425

1 Answers

0
votes

Ignoring/deleting full stops and surplus spaces, please try:

=index(Sheet1!B:B,match(REGEXEXTRACT(A1,ArrayFormula(textjoin("|",1,Sheet1!A:A))),Sheet1!A:A,0))