SOLVED
Home

Which formula to create?

%3CLINGO-SUB%20id%3D%22lingo-sub-795570%22%20slang%3D%22en-US%22%3EWhich%20formula%20to%20create%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-795570%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20working%20on%20a%20spreadsheet.%20On%20the%20first%20worksheet%20of%20my%20workbook%2C%20I%20have%20a%20table%20with%20each%20person%20and%20their%20respective%20information%20(age%2C%20their%20choice%20of%20A%20or%20B%2C%20ect.).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20my%20data%20tab%2C%20I%20have%20the%20types%20and%20the%20rates%20necessary.%20Our%20rates%20are%20dependent%20by%20age%2C%20so%20everyone's%20rates%20are%20different%20depending%20on%20what%20their%20chosen%20type%20of%20A%20or%20B%20is%20and%20what%20their%20age%20is.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20my%20first%20worksheet%2C%20I%20would%20like%20to%20set%20up%20a%20function%20that%20pulls%20the%20amount%20from%20the%20data%20table%20dependent%20on%20the%20age%20and%20the%20type%20of%20choice%2C%20so%20I%20do%20not%20have%20to%20go%20look%20it%20up%20every%20time.%20I%20do%20not%20how%20to%20do%20this%20with%20two%20determining%20factors%20instead%20of%20one.%20Can%20anyone%20help%20out%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-795570%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-795621%22%20slang%3D%22en-US%22%3ERe%3A%20Which%20formula%20to%20create%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-795621%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F389331%22%20target%3D%22_blank%22%3E%40Chels405%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWould%20you%20mind%20setting%20up%20a%20sample%20worksheet%20to%20illustrate%20what%20you%20are%20describing%3F%20This%20will%20help%20me%20better%20answer%20your%20question.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3CBR%20%2F%3EPReagan%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-795646%22%20slang%3D%22en-US%22%3ERe%3A%20Which%20formula%20to%20create%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-795646%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F360420%22%20target%3D%22_blank%22%3E%40PReagan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENot%20a%20problem!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20under%20my%20Master%20tab%2C%20I%20would%20like%20the%20amount%20to%20show%20up%20in%20the%20highlighted%20area%20under%20Cost%20(Column%20E)%20from%20my%20Data%20tab%2C%20but%20the%20determining%20factors%20of%20the%20cost%20is%20dependent%20on%20Choice%20A%20or%20B%20and%20the%20age%20of%20the%20individual%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20style%3D%22width%3A%20350px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F126229iCE19583505383505%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22Excel%201.PNG%22%20title%3D%22Excel%201.PNG%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20style%3D%22width%3A%20187px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F126230iBC98389BF2B132F0%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22Excel%202.PNG%22%20title%3D%22Excel%202.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-795673%22%20slang%3D%22en-US%22%3ERe%3A%20Which%20formula%20to%20create%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-795673%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F389331%22%20target%3D%22_blank%22%3E%40Chels405%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20cell%20E2%2C%20that%20could%20be%20something%20like%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3Dif(C2%3D%22A%22%2CVLOOKUP(D2%2C'Data'!%24A%243%3A%24C%2422%2C2%2Cfalse)%2CIF(C2%3D%22B%22%2CVLOOKUP(D2%2C'Data'!%24A%243%3A%24C%2422%2C3%2Cfalse)%2C%22No%20Result%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-795680%22%20slang%3D%22en-US%22%3ERe%3A%20Which%20formula%20to%20create%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-795680%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F360420%22%20target%3D%22_blank%22%3E%40PReagan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%20I%20will%20try%20it%20out!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-795727%22%20slang%3D%22en-US%22%3ERe%3A%20Which%20formula%20to%20create%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-795727%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F389331%22%20target%3D%22_blank%22%3E%40Chels405%3C%2FA%3E%26nbsp%3BI've%20attached%20a%20spreadsheet%20that%20has%20a%20%22shortcut%22%20of%20sorts%20in%20it.%20You'll%20want%20to%20spot%20check%20it%20but%20this%20would%20be%20a%20slightly%20quicker%20route.%20Rather%20than%20explain%20it%20all%20out%2C%20I%20just%20created%20a%20spreadsheet%20and%20showed%20the%20formulas%20next%20to%20each%20of%20the%20cells%20so%20you%20can%20see%20what%20I%20did.%20Hope%20it%20helps.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-795966%22%20slang%3D%22en-US%22%3ERe%3A%20Which%20formula%20to%20create%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-795966%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F389331%22%20target%3D%22_blank%22%3E%40Chels405%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20order%20to%20generate%20meaningful%20references%20I%20placed%20both%20the%20Master%20data%20and%20the%20Options%20in%20appropriately%20named%20Tables.%26nbsp%3B%20Given%20that%20there%20exist%20two%20choices%20for%20the%20lookup%20data%20I%20first%20provided%20the%20formula%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E%3D%20IF(%20%5B%40%5BA%20or%20B%5D%5D%3D%22A%22%2C%20Choice%5BA%5D%2C%20Choice%5BB%5D%20)%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CFONT%3Eto%20select%20the%20relevant%20column.%26nbsp%3B%20Then%2C%20since%20the%20look%20up%20data%20is%20sorted%20in%20age%20order%20I%20used%20the%20LOOKUP%20function%20to%20return%20the%20cost%20from%20the%20corresponding%20cell%20of%20that%20column.%26nbsp%3B%20Nesting%20the%20formulae%20one%20obtains%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E%3D%20LOOKUP(%5B%40Age%5D%2C%20Choice%5BAge%5D%2C%20IF(%20%5B%40%5BA%20or%20B%5D%5D%3D%22A%22%2C%20Choice%5BA%5D%2C%20Choice%5BB%5D%20)%20)%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3EA%20variant%20is%20to%20use%20a%20defined%20Name%20'%3CSTRONG%3EAgeRelatedCost%3C%2FSTRONG%3E'%20to%20refer%20to%20the%20first%20formula%2C%20in%20which%20case%20the%20second%20reduces%20to%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E%3D%20LOOKUP(%20%5B%40Age%5D%2C%20Choice%5BAge%5D%2C%20AgeRelatedCost%20)%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3EOverall%2C%20the%20objective%20is%20not%20to%20create%20short%20formulas%3B%20it%20is%20to%20create%20clear%20formulas%20that%20are%20easy%20to%20read.%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Chels405
New 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

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.

Related Conversations