Forum Discussion

Chels405's avatar
Chels405
Copper Contributor
Aug 08, 2019
Solved

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 ...
excel
Formulas and Functions
training
  • PReagan's avatar
    PReagan
    Aug 08, 2019

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

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Aug 08, 2019

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.