IFS INDEX and MATCH based on Dropdown List

[SRJRTL]

Hi Experts!


I am working on a pricing tool and have the majority of the tool working.

I have tables called "Dayspd1" 2, 3...etc... created in different named Sheets based on the product (Pd) type.

I have the formula working to select the number of days in one cell (C8), then the number of units in the next cell (C10), to produce the price output automatically. ((C4="Product1"),INDEX(Dayspd1,MATCH(Tool!$C8,INDEX(Days,,1),0),MATCH(Tool!C$10,INDEX(Days,1,),0)))

This all works, however I need to make the formula adapt so if I select "Product2" from the dropdown in C4, It will then use ((C4="Product2"),INDEX(Dayspd2,MATCH(Tool!$C8,INDEX(Dayspd2,,1),0),MATCH(Tool!C$10,INDEX(Dayspd2,1,),0)))

And of course products 3,4,5 onward

I have tried IFS and IF in combination with the above and keep erroring out.

Any help would be amazing, Im no expert but usually work it out in the end, but this has me stumped!

 

[bobsan42]

You need to "calculate" the table name in a cell somewhere (for example using VLOOKUP or something), then use the result with INDIRECT function e.g. INDEX(INDIRECT(C5), ....

 

[SRJRTL]

You need to "calculate" the table name in a cell somewhere (for example using VLOOKUP or something), then use the result with INDIRECT function e.g. INDEX(INDIRECT(C5), ....

Thank you for your help. I have to say I dont quite follow your example, sorry!

 

[SRJRTL]

You need to "calculate" the table name in a cell somewhere (for example using VLOOKUP or something), then use the result with INDIRECT function e.g. INDEX(INDIRECT(C5), ....

Got it! Thank you so much!

 

[bobsan42]

Sorry if I have not explained too well.
Glad you worked it out.