SOLVED

Formulas

%3CLINGO-SUB%20id%3D%22lingo-sub-3284637%22%20slang%3D%22en-US%22%3EFormulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3284637%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20an%20excel%20spreadsheet%20that%20has%20information%20in%20a%20table.%20The%20table%20shows%20prices%20for%20a%20flight%20based%20on%20destination%20and%20class%20(first%20or%20economy).%20I%20am%20trying%20to%20make%20it%20so%20that%20when%20the%20input%20is%20a%20destination%20and%20type%20of%20class%2C%20the%20output%20becomes%20a%20price%20for%20that%20%22flight%22%20if%20you%20will.%20Anyone%20know%20the%20correct%20formula%20to%20do%20this%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3284637%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3286632%22%20slang%3D%22en-US%22%3ERe%3A%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3286632%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1362544%22%20target%3D%22_blank%22%3E%40CFrench11%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20oddity%20is%20that%20SWITCH%20does%20not%20appear%20to%20spill%20in%20order%20to%20return%20a%20full%20column%20of%20prices%20in%20the%20picture%20you%20show.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20I%20said%20earlier%20though%2C%20it%20is%20not%20a%20case%20of%20'the%20correct%20solution'.%26nbsp%3B%20I%20could%20write%20solutions%20using%20CHOOSE%2C%20SWITCH%2C%20a%20second%20XLOOKUP%20or%20a%20second%20MATCH%20embedded%20within%20the%20INDEX.%26nbsp%3B%20Any%20of%20the%20formulae%20may%20be%20placed%20within%20a%20Lambda%20function%20or%20left%20as%20part%20of%20the%20main%20formula%3B%20it%20is%20possible%20to%20search%20for%20class%20first%20and%20than%20destination%20or%20the%20reverse.%26nbsp%3B%20As%20long%20as%20you%20have%20something%20that%20works%20for%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3286418%22%20slang%3D%22en-US%22%3ERe%3A%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3286418%22%20slang%3D%22en-US%22%3EI%20copied%20the%20one%20in%20your%20example%20exactly.%20It%20might%20not%20be%20possible%20to%20do%20what%20I%20want%20given%20the%20circumstances%2C%20so%20thanks%20for%20working%20with%20me.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3286417%22%20slang%3D%22en-US%22%3ERe%3A%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3286417%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3BDo%20you%20know%20how%20to%20fix%20the%20problem%20with%20the%20SWITCH%20formula%3F%20Whenever%20I%20put%20in%20the%20destination%20and%20class%20the%20price%20comes%20out%20for%20the%20first%20class%20regardless%20of%20the%20class%20I%20pick.%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Switch%20Formula.jpg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F364413iAC73ACD221C77DA9%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Switch%20Formula.jpg%22%20alt%3D%22Switch%20Formula.jpg%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Price%20Formula.jpg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F364415iFA4F035340067CDC%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Price%20Formula.jpg%22%20alt%3D%22Price%20Formula.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3285623%22%20slang%3D%22en-US%22%3ERe%3A%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3285623%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1362544%22%20target%3D%22_blank%22%3E%40CFrench11%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20write%20for%20newer%20versions%20of%20Excel.%26nbsp%3B%20Perhaps%20this%20will%20work.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%20INDEX(%0A%20%20%20%20SWITCH(%5B%40Class%5D%2C%20%22First%20class%22%2C%20Price%5BFirst%20class%5D%2C%20%22Economy%22%2CPrice%5BEconomy%5D)%2C%0A%20%20%20%20MATCH(%5B%40Destination%5D%2C%20Price%5BDestination%5D%2C%200)%0A%20%20)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EEven%20SWITCH%20requires%202016%20(I%20think).%26nbsp%3B%20I%20like%20it%20because%20my%20objective%20is%20to%20make%20the%20code%20as%20readable%20as%20possible.%26nbsp%3B%20Other%20developers%20aim%20at%20as%20fast%20as%20possible%20to%20write%20or%20as%20concise%20as%20possible.%26nbsp%3B%20Normal%20users%20do%20not%20regard%20their%20formulas%20as%20code.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3285253%22%20slang%3D%22en-US%22%3ERe%3A%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3285253%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20a%20side%20note%20I%20was%20playing%20around%20with%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22REF%20problem.jpg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F364015iD449D04D89D46C3B%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22REF%20problem.jpg%22%20alt%3D%22REF%20problem.jpg%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Value%20Error.jpg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F364016i770361E84B0D52F4%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Value%20Error.jpg%22%20alt%3D%22Value%20Error.jpg%22%20%2F%3E%3C%2FSPAN%3E%20substituting%20xlookup%20with%20match%2Findex%20like%20you%20said.%20I%20got%20this%20reference%20error%2C%20as%20well%20as%20value%20error.%20Any%20idea%20how%20to%20fix%20that%20or%20what%20I%20did%20wrong%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3285240%22%20slang%3D%22en-US%22%3ERe%3A%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3285240%22%20slang%3D%22en-US%22%3EThank%20You!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3285163%22%20slang%3D%22en-US%22%3ERe%3A%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3285163%22%20slang%3D%22en-US%22%3EINDEX%2FMATCH%20should%20work%20fine%20on%20legacy%20Excel.%20You%20could%20also%20use%20a%20second%20MATCH%20to%20replace%20the%20SWITCH%20function%20by%20a%20column%20index%20to%20use%20within%20the%20INDEX.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3285127%22%20slang%3D%22en-US%22%3ERe%3A%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3285127%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1362544%22%20target%3D%22_blank%22%3E%40CFrench11%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EXLOOKUP%20is%20available%20to%20'ordinary'%20users%20of%20Microsoft%20365%20and%20Office%202021%2C%20but%20not%20to%20users%20of%20older%20versions%2C%20and%20perhaps%20not%20to%20users%20of%20Microsoft%20365%20with%20delayed%20updates.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3285117%22%20slang%3D%22en-US%22%3ERe%3A%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3285117%22%20slang%3D%22en-US%22%3EIs%20there%20an%20alternative%20to%20xlookup%2C%20because%20I%20do%20not%20have%20the%20option%20for%20xlookup.%20Is%20it%20still%20true%20that%20xlookup%2C%20as%20well%20as%20Lambda%20is%20only%20'rolled%20out'%20for%20insiders%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3284809%22%20slang%3D%22en-US%22%3ERe%3A%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3284809%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1362544%22%20target%3D%22_blank%22%3E%40CFrench11%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOr%20then%2C%20to%20pursue%20my%20interests%20rather%20than%20the%20user%20requirement%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%20Price%CE%BB(Destination%2CClass)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewhere%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EPrice%CE%BB%20%3D%20LAMBDA(dest%2C%20cl%2C%0A%20%20%20%20LET(%0A%20%20%20%20%20%20%20%20priceList%2C%20SWITCH(%0A%20%20%20%20%20%20%20%20%20%20%20%20cl%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%22First%22%2C%20%20%20PriceTbl%5BFirst%5D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%22Economy%22%2C%20PriceTbl%5BEconomy%5D%0A%20%20%20%20%20%20%20%20)%2C%0A%20%20%20%20%20%20%20%20XLOOKUP(Dest%2C%20PriceTbl%5BDestination%5D%2C%20priceList)%0A%20%20%20%20)%0A)%3B%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3284769%22%20slang%3D%22en-US%22%3ERe%3A%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3284769%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1362544%22%20target%3D%22_blank%22%3E%40CFrench11%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20don't%20think%20it%20is%20a%20case%20of%20a%20'correct'%20answer.%26nbsp%3B%20There%20are%20many%20variations%20that%20will%20work%20and%20it%20also%20depends%20upon%20the%20version%20of%20Excel%20that%20you%20use.%26nbsp%3B%20Using%20modern%20functions%20but%20stopping%20short%20of%20Lambda%20functions%20one%20might%20have%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%20XLOOKUP(%0A%20%20%20%20Destination%2CPriceTbl%5BDestination%5D%2C%0A%20%20%20%20%20%20SWITCH(Class%2C%0A%20%20%20%20%20%20%20%20%22First%22%2CPriceTbl%5BFirst%5D%2C%0A%20%20%20%20%20%20%20%20%22Economy%22%2CPriceTbl%5BEconomy%5D)%0A%20%20)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20493px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F363888i8A9A78ECCC9D60C2%2Fimage-dimensions%2F493x321%3Fv%3Dv2%22%20width%3D%22493%22%20height%3D%22321%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3284660%22%20slang%3D%22en-US%22%3ERe%3A%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3284660%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1362544%22%20target%3D%22_blank%22%3E%40CFrench11%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20would%20help%20if%20you%20made%20a%20small%20sample%20workbook%20available%20through%20OneDrive%2C%20Google%20Drive%2C%20Dropbox%20or%20similar.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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? 

12 Replies

@CFrench11 

It would help if you made a small sample workbook available through OneDrive, Google Drive, Dropbox or similar.

best response confirmed by CFrench11 (Occasional Contributor)
Solution

@CFrench11 

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

image.png

@CFrench11 

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

 

 

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?

@CFrench11 

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.

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.
Thank You!

@Peter Bartholomew 

On a side note I was playing around withREF problem.jpgValue Error.jpg 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?

@CFrench11 

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.

 

@Peter Bartholomew 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. Switch Formula.jpgPrice Formula.jpg

I copied the one in your example exactly. It might not be possible to do what I want given the circumstances, so thanks for working with me.

@CFrench11 

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.