CODEWITHSUNDEEP
excelvbaexcel-2016
sql scholar
sql scholar

Reputation: 199

INDEX & MATCH with switchable ranges

I have a simple INDEX&MATCH formula for cost distribution in Excel 2016. Total cost in D4 is distributed in COST DISTRIBUTION table, based on ratios set in MARKETING or SERVICE tables. Currently the formula in COST DISTIBUTION table is:

=$D$4*INDEX(Marketing_ratio;MATCH(F3;Marketing_area;0))

With this setup the formula is fixed to MARKETING table cost allocation logic, which I don't want it to be. In some instances I would like to use the distribution ratio from SERVICE table (there can be several others) and it would look something like:

=$D$4*INDEX(Service_ratio;MATCH(F3;Service_area;0))

Ideally I'd like to use some value in Distribution key field to adjust correct named range in the formula. E.g. - still use the similar INDEX&MATCH function, but change the ranges where the formula turns to.

Could this be done? Ideally without macros, however using a macro for this will be OK too if Excel native formulas won't be enough.

enter image description here

Upvotes: 1

Views: 44

Answers (1)

Domenic
Domenic

Reputation: 8114

You can use a combination of CHOOSE/MATCH to return the desired range...

=$D$4*INDEX(CHOOSE(MATCH($D$10,{"Marketing","Service"},0),Marketing_ratio,Service_ratio),MATCH(F3,CHOOSE(MATCH($D$10,{"Marketing","Service"},0),Marketing_area,Service_area),0))

...where D10 contains the distribution key, such as Marketing or Service.

Upvotes: 2

Related Questions