Forum Discussion
Formulas
I don't think it is a case of a 'correct' answer. There are many variations that will work and it also depends upon the version of Excel that you use. Using modern functions but stopping short of Lambda functions one might have
= XLOOKUP( Destination,PriceTbl[Destination], SWITCH(Class, "First",PriceTbl[First], "Economy",PriceTbl[Economy]) )
On a side note I was playing around with substituting xlookup with match/index like you said. I got this reference error, as well as value error. Any idea how to fix that or what I did wrong?
I write for newer versions of Excel. Perhaps this will work.
= INDEX( SWITCH([@Class], "First class", Price[First class], "Economy",Price[Economy]), MATCH([@Destination], Price[Destination], 0) )Even SWITCH requires 2016 (I think). I like it because my objective is to make the code as readable as possible. Other developers aim at as fast as possible to write or as concise as possible. Normal users do not regard their formulas as code.
PeterBartholomew1 Do you know how to fix the problem with the SWITCH formula? Whenever I put in the destination and class the price comes out for the first class regardless of the class I pick.
- Thank You!