Forum Discussion
Formulas
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?
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]) )
12 Replies
Or then, to pursue my interests rather than the user requirement,
= Priceλ(Destination,Class)where
Priceλ = LAMBDA(dest, cl, LET( priceList, SWITCH( cl, "First", PriceTbl[First], "Economy", PriceTbl[Economy] ), XLOOKUP(Dest, PriceTbl[Destination], priceList) ) );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]) )- Is there an alternative to xlookup, because I do not have the option for xlookup. Is it still true that xlookup, as well as Lambda is only 'rolled out' for insiders?
- INDEX/MATCH should work fine on legacy Excel. You could also use a second MATCH to replace the SWITCH function by a column index to use within the INDEX.
It would help if you made a small sample workbook available through OneDrive, Google Drive, Dropbox or similar.
