Apr 13 2022 07:52 AM
Apr 13 2022 07:52 AM
I have an excel spreadsheet that has information in a table. The table shows prices for a flight based on destination and class (first or economy). I am trying to make it so that when the input is a destination and type of class, the output becomes a price for that "flight" if you will. Anyone know the correct formula to do this?
Apr 13 2022 09:21 AMSolution
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]) )
Apr 13 2022 09:40 AM - edited Apr 13 2022 09:45 AM
Or then, to pursue my interests rather than the user requirement,
Priceλ = LAMBDA(dest, cl, LET( priceList, SWITCH( cl, "First", PriceTbl[First], "Economy", PriceTbl[Economy] ), XLOOKUP(Dest, PriceTbl[Destination], priceList) ) );
Apr 13 2022 02:19 PM
Apr 13 2022 03:14 PM
Apr 14 2022 04:18 AM
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.
Apr 14 2022 06:06 PM
Apr 15 2022 04:52 AM
The oddity is that SWITCH does not appear to spill in order to return a full column of prices in the picture you show.
As I said earlier though, it is not a case of 'the correct solution'. I could write solutions using CHOOSE, SWITCH, a second XLOOKUP or a second MATCH embedded within the INDEX. Any of the formulae may be placed within a Lambda function or left as part of the main formula; it is possible to search for class first and than destination or the reverse. As long as you have something that works for you.