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]) )
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.
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?
XLOOKUP is available to 'ordinary' users of Microsoft 365 and Office 2021, but not to users of older versions, and perhaps not to users of Microsoft 365 with delayed updates.
