Google Sheets reverse vlookup partial match?

I'm trying to return the length, width, height, and weight for each item (A1:E29) based on the data provided (G1:K10) I know how to do a vlookup partial match but this is the other way around. • Welcome. Please remember that as per site guidelines when an answer addresses your question, you should accept it and even upvote it so others can benefit as well. Apr 21 '21 at 21:17

=INDEX(IF(LEN(A2:A),
VLOOKUP(REGEXEXTRACT(A2:A,"^.+ Monitor|^.+ Chair"),
G2:K,{2,3,4,5},0),)) The regex says:

• .+ Monitor extract everything before and including Monitor
• | OR
• .+ Chair extract everything before and including Chair
• Thank you for your solution marikamitsos, it works perfectly! Apr 21 '21 at 22:40
• Thank you for taking the time to accept. Apr 21 '21 at 22:55

You can try this:

=arrayformula(IF(LEN(A2:A),
VLOOKUP(REGEXEXTRACT(A2:A,"[^\s]+"),
{REGEXEXTRACT(G2:G10,"[^\s]+"),H2:K10},
{2,3,4,5},
0),
))

Basically when searching for example: 2x3 (60x90 cm) Monitor Banana Orange

We use 2x3 as the search key, so we extract it using a regular expression. Here we use a simple one: extract what's before the first space REGEXEXTRACT(A2:A,"[^\s]+"). Then we build our range for the VLOOKUP using again the same regular expression for the first column, along with the rest of the table, so we obtain {REGEXEXTRACT(G2:G10,"[^\s]+"),H2:K10}. Finally, if there is a match we return the following columns {2,3,4,5} from the table we've just considered. Regular expression explained:

• \s a space
• ^ the first
• ^\s the first match of a space
• [] a group
• [^\s] a group found before the first match of a space
• + more than one character if possible
• [^\s]+ extract a group of characters before the first occurrence of a space
• Thanks for your solution and the thorough explanation Nabnub. What if I want to use a more detailed search key that includes everything before the closing parentheses plus one word after that i.e. 2x3 (60x90 cm) Monitor. And in that case, what happens if the name does not have parentheses i.e. 4x6 Gaming Apr 21 '21 at 21:12
• Since you did not specify it, I've just answered your question as it stands. And my answer seems working with the sample of data you provided, unless the rest of your data is different. If not, then my answer does not really need to be updated I think. Apr 22 '21 at 7:25