Use SWITCH() instead of nested IFS - possible?

[dmj120]

Can I use SWITCH() to streamline the below? This workbook is very large so I'm trying to keep efficiency at the top of the list.

Basically, I'm matching one cell to a range and need the header name of the corresponding match.

=IFERROR(IF([@[Priced as]]=[@[Like Mdl Pricing]], pricebook[[#Headers],[Like Mdl Pricing]],
   IF([@[Priced as]]=[@[AvgRL Price Device]],pricebook[[#Headers],[AvgRL Price Device]],
   IF([@[Priced as]]=[@[Avg RL Price Model]],pricebook[[#Headers],[Avg RL Price Model]],
   IF([@[Priced as]]=[@[Standard Price]],pricebook[[#Headers],[Standard Price]],
   IF([@[Priced as]]=[@[Avg Budget (Exp rpt)]],pricebook[[#Headers],[Avg Budget (Exp rpt)]],
   IF([@[Priced as]]=[@[RFP FSWP Avg.]],pricebook[[#Headers],[RFP FSWP Avg.]])))))),
"nada")

 

[ExcelToDAX]

How about:

    SWITCH([@[Priced as]],
        [@[Like Mdl Pricing]], pricebook[[#Headers],[Like Mdl Pricing]],
        [@[AvgRL Price Device]], pricebook[[#Headers],[AvgRL Price Device]],
        [@[Avg RL Price Model]], pricebook[[#Headers],[Avg RL Price Model]],
        [@[Standard Price]], pricebook[[#Headers],[Standard Price]],
        [@[Avg Budget (Exp rpt)]], pricebook[[#Headers],[Avg Budget (Exp rpt)]],
        [@[RFP FSWP Avg.]], pricebook[[#Headers],[RFP FSWP Avg.]],
        "nada"
    )

 

[dmj120]

thanks @ExcelToDAX works perfectly!!

 

[Fluff]

This workbook is very large so I'm trying to keep efficiency at the top of the list.

In that case you are better off with using your formula, rather than switch (or ifs) as they will evaluate everything, which a nested if does not do.

 

[djclements]

Basically, I'm matching one cell to a range and need the header name of the corresponding match.

Is the range contiguous? In other words, do the columns appear next to each other, in the same order that you've listed, with nothing else in between?

If so, you could simply use INDEX-MATCH or XLOOKUP:

=IFERROR(INDEX(pricebook[[#Headers],[Like Mdl Pricing]:[RFP FSWP Avg.]], MATCH([@[Priced as]], pricebook[@[Like Mdl Pricing]:[RFP FSWP Avg.]], 0)), "nada")

=XLOOKUP([@[Priced as]], pricebook[@[Like Mdl Pricing]:[RFP FSWP Avg.]], pricebook[[#Headers],[Like Mdl Pricing]:[RFP FSWP Avg.]], "nada")

If not, you could also try something along these lines:

=XLOOKUP(
    [@[Priced as]],
    INDEX(([@[Like Mdl Pricing]],[@[AvgRL Price Device]],[@[Avg RL Price Model]],[@[Standard Price]],[@[Avg Budget (Exp rpt)]],[@[RFP FSWP Avg.]]),,,{1,2,3,4,5,6}),
    {"Like Mdl Pricing","AvgRL Price Device","Avg RL Price Model","Standard Price","Avg Budget (Exp rpt)","RFP FSWP Avg."},
    "nada"
)

...where the [area_num] argument of INDEX is used to convert the non-contiguous range reference into an array.

OR:

=XLOOKUP(
    [@[Priced as]],
    INDEX(pricebook[@],,{3,5,7,9,10,12}),
    {"Like Mdl Pricing","AvgRL Price Device","Avg RL Price Model","Standard Price","Avg Budget (Exp rpt)","RFP FSWP Avg."},
    "nada"
)

...where the [column_num] argument of INDEX is used to select the applicable columns from "This Row" (please note: {3,5,7,9,10,12} is just an example; adjust the column numbers to align with your actual dataset).