1

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.

https://docs.google.com/spreadsheets/d/1-6MdV0EXP2_6hMM81sqoJDcWuB-q4GeNRE5xlmyDgMQ/edit?usp=sharing

enter image description here

1
  • 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. – marikamitsos Apr 21 at 21:17
0

Please try this regex formula

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

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
2
  • 1
    Thank you for your solution marikamitsos, it works perfectly! – jicchi Apr 21 at 22:40
  • Thank you for taking the time to accept. – marikamitsos Apr 21 at 22:55
0

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.

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
2
  • 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 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 at 7:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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