Forum Discussion

Stuznet's avatar
Stuznet
Icon for Helper V rankHelper V
7 years ago
Solved

How to turn Lookupvalue in to Switch Function

Hi all,

 

On the Excel I have 2 Worksheets.

Table 1

 

If Table1[Var] match the Table2[Min] then give me the Percentage

Is it possible to elimnate index/lookup table and use the Switch function? If yes, How do I turn this Index function into a Switch Function? 

 

=INDEX(Table2[Percentage],MATCH([@Var],Table2[Min],1))

 

 

Lookup Table (Table2)

 

 

 Thanks you :) 

 

 

 

Need Help
Show and Tell
Tutorial Requests
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi Stuznet,

     

    Here we can use the IF function to create a calculated column to work on it.

     

    Column = 
IF (
    Table1[var] > -1000
        && Table1[var] < -0.25,
    "<(25%)",
    IF (
        Table1[var] > -0.25
            && Table1[var] < -0.1,
        "(25%) - (10%)",
        IF (
            Table1[var] > -0.1
                && Table1[var] < -0.03,
            "(10%)-(3%)",
            IF (
                Table1[var] > -0.03
                    && Table1[var] < 0.03,
                "(3%)-3%",
                IF (
                    Table1[var] > 0.03
                        && Table1[var] < 0.1,
                    "3%-10%",
                    IF (
                        Table1[var] > 0.1
                            && Table1[var] < 0.25,
                        "10%-25%",
                        IF ( Table1[var] > 0.25, ">25%" )
                    )
                )
            )
        )
    )
)

     

    For more details, please check the pbix as attached.

     

    Regards,

    Frank

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    HI Stuznet v-frfei-msft

     

    This formula will achieve the same desired result :)

    Column 2 = 
MINX (
    TOPN ( 1, FILTER ( Table2, Table1[var] > Table2[Min] ), [Min], DESC ),
    [Percentage]
)

3 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi Stuznet,

     

    Here we can use the IF function to create a calculated column to work on it.

     

    Column = 
IF (
    Table1[var] > -1000
        && Table1[var] < -0.25,
    "<(25%)",
    IF (
        Table1[var] > -0.25
            && Table1[var] < -0.1,
        "(25%) - (10%)",
        IF (
            Table1[var] > -0.1
                && Table1[var] < -0.03,
            "(10%)-(3%)",
            IF (
                Table1[var] > -0.03
                    && Table1[var] < 0.03,
                "(3%)-3%",
                IF (
                    Table1[var] > 0.03
                        && Table1[var] < 0.1,
                    "3%-10%",
                    IF (
                        Table1[var] > 0.1
                            && Table1[var] < 0.25,
                        "10%-25%",
                        IF ( Table1[var] > 0.25, ">25%" )
                    )
                )
            )
        )
    )
)

     

    For more details, please check the pbix as attached.

     

    Regards,

    Frank