 • 510K Members
• 6,103 Online
• 607K Conversations
SOLVED

New Contributor

# 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

# Re: Which formula to create?

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

# Re: Which formula to create?

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:  Solution

# Re: Which formula to create?

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

# Re: Which formula to create?

Thank you! I will try it out!

# Re: Which formula to create?

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

# Re: Which formula to create?

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.

Related Conversations
formula for data calculation
aayushman_mishra in Excel on
10 Replies
Creating A Sublist
zjohnson in Excel on
5 Replies
Excel Forumla to exclude empty cells.
ulken2019 in Excel on
6 Replies