Forum Discussion
In desperate need of a formula for a -basically- simple task
Here is the tweaked formula...
=INDEX(Table1[[accessory 1]:[accessory 3]],MATCH([@product],Table1[product],0),IF(MOD(COUNTIF(F$3:F3,F3),COUNTA(INDEX(Table1[[accessory 1]:[accessory 3]],MATCH([@product],Table1[product],0),)))=0,COUNTA(INDEX(Table1[[accessory 1]:[accessory 3]],MATCH([@product],Table1[product],0),)),MOD(COUNTIF(F$3:F3,F3),COUNTA(INDEX(Table1[[accessory 1]:[accessory 3]],MATCH([@product],Table1[product],0),)))))
We're almost there! The only thing is: with this formula product 3 would be tested with accessories a-d-a-a-d-a-a-d-a-a-d-a, whil it should be a-d-a-d-a-d-a-d. This way I would test twice as much with 'a' as with 'd'. That would corrupt the outcome...
Here is the tweaked formula...
=INDEX(Table1[[accessory 1]:[accessory 3]],MATCH([@product],Table1[product],0),IF(MOD(COUNTIF(F$3:F3,F3),COUNTA(INDEX(Table1[[accessory 1]:[accessory 3]],MATCH([@product],Table1[product],0),)))=0,COUNTA(INDEX(Table1[[accessory 1]:[accessory 3]],MATCH([@product],Table1[product],0),)),MOD(COUNTIF(F$3:F3,F3),COUNTA(INDEX(Table1[[accessory 1]:[accessory 3]],MATCH([@product],Table1[product],0),)))))
Holy crap Subodh_Tiwari_sktneer !
That looks fantastic! 🙂 I will build it in my masterfile and post the outcome here. This is really great stuff, thanks a lot!
Kind regards,
Arno
You're welcome Arno!
Thanks for the feedback.
Please don't forget to mark the post with proposed solution as an Accepted Answer and hit the like button if you liked the solution provided. 🙂