Google Sheets reverse vlookup partial match?

You can try this:


Basically when searching for example: 2×3 (60×90 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.

enter image description here

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