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.


enter image description here

  • 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, 2021 at 21:17

2 Answers 2


Please try this regex formula

    VLOOKUP(REGEXEXTRACT(A2:A,"^.+ Monitor|^.+ Chair"),

enter image description here

(please -as always- adjust formula according to your ranges and locale)

The regex says:

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

You can try this:


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.

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
  • 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
    – jicchi
    Apr 21, 2021 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.
    – Nabnub
    Apr 22, 2021 at 7:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.