How to turn Lookupvalue in to Switch Function

[Stuznet]

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 🙂 

[v-frfei-msft · Accepted solution]

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

[v-frfei-msft · Accepted solution]

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 · Accepted solution]

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

[Stuznet]

@Zubair_Muhammad, @v-frfei-msft. Thank you so much, I accepted both solutions. It Worked!! 🙂