SOLVED

Which formula to create?

Copper Contributor

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

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

@PReagan 

 

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:

 

Excel 1.PNGExcel 2.PNG

best response confirmed by Chels405 (Copper Contributor)
Solution

Hello @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")

@PReagan 

 

Thank you! I will try it out! 

@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.

@Chels405 

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.

1 best response

Accepted Solutions
best response confirmed by Chels405 (Copper Contributor)
Solution

Hello @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")

View solution in original post