Aug 08 2019 09:00 AM - edited Aug 08 2019 09:04 AM
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?
Aug 08 2019 09:16 AM
Hello @Chels405,
Would you mind setting up a sample worksheet to illustrate what you are describing? This will help me better answer your question.
Thank you!
PReagan
Aug 08 2019 09:26 AM
Not a problem!
So under my Master tab, I would like the amount to show up in the highlighted area under Cost (Column E) from my Data tab, but the determining factors of the cost is dependent on Choice A or B and the age of the individual:
Aug 08 2019 09:39 AM - edited Aug 08 2019 09:40 AM
SolutionHello @Chels405,
In cell E2, that could be something like:
=if(C2="A",VLOOKUP(D2,'Data'!$A$3:$C$22,2,false),IF(C2="B",VLOOKUP(D2,'Data'!$A$3:$C$22,3,false),"No Result")
Aug 08 2019 10:07 AM
@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.
Aug 08 2019 12:23 PM
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.
Aug 08 2019 09:39 AM - edited Aug 08 2019 09:40 AM
SolutionHello @Chels405,
In cell E2, that could be something like:
=if(C2="A",VLOOKUP(D2,'Data'!$A$3:$C$22,2,false),IF(C2="B",VLOOKUP(D2,'Data'!$A$3:$C$22,3,false),"No Result")