Forum Discussion
Which formula to create?
I am working on a spreadsheet. On the first worksheet of my workbook, I have a table with each person and their respective information (age, their choice of A or B, ect.).
In my data tab, I have the types and the rates necessary. Our rates are dependent by age, so everyone's rates are different depending on what their chosen type of A or B is and what their age is.
On my first worksheet, I would like to set up a function that pulls the amount from the data table dependent on the age and the type of choice, so I do not have to go look it up every time. I do not how to do this with two determining factors instead of one. Can anyone help out?
6 Replies
In order to generate meaningful references I placed both the Master data and the Options in appropriately named Tables. Given that there exist two choices for the lookup data I first provided the formula
= IF( [@[A or B]]="A", Choice[A], Choice[B] )
to select the relevant column. Then, since the look up data is sorted in age order I used the LOOKUP function to return the cost from the corresponding cell of that column. Nesting the formulae one obtains
= LOOKUP([@Age], Choice[Age], IF( [@[A or B]]="A", Choice[A], Choice[B] ) )
A variant is to use a defined Name 'AgeRelatedCost' to refer to the first formula, in which case the second reduces to
= LOOKUP( [@Age], Choice[Age], AgeRelatedCost )
Overall, the objective is not to create short formulas; it is to create clear formulas that are easy to read.
Chels405 I've attached a spreadsheet that has a "shortcut" of sorts in it. You'll want to spot check it but this would be a slightly quicker route. Rather than explain it all out, I just created a spreadsheet and showed the formulas next to each of the cells so you can see what I did. Hope it helps.
