I need a formula for the VariantAttribute column, which fills in the ProductAttribute value based on the first 4 characters of the VariantID
Desired result:
| ProductID | ProductAttribute | VariantID | VariantAttribute | |
|---|---|---|---|---|
| ABCD | blue | ABCD-xx | blue | |
| BCDE | black | ABCD-yy | blue | |
| CDEF | orange | BCDE-vv | black | |
| DEFG | blue | BCDE-ww | black | |
| CDEF-uu | orange | |||
| DEFG-zz | blue | |||
| ABCD-uu | blue |
I tried to combine ARRAYFORMULA() with INDEX(MATCH())but failed, obviously because I'm not able to specify a search range within ARRAYFORMULA()
How can I get the desired result?
CodePudding user response：
Assuming the dash ("-") is consistent.
=ARRAYFORMULA(IF(D2:D="",,VLOOKUP(INDEX(SPLIT(D2:D,"-"),,1),A:B,2,0))