SQL query to Excel formula conversion-CodePudding

How to convert this SQL query to an Excel formula?

CASE 
    WHEN UPPER(V_out) = 'GOOGLE' OR (UPPER(V_out) = 'APPLE' AND dis > 800) 
        THEN 2 
        ELSE 
            CASE 
                WHEN UPPER(V_out) IN ('APPLE', 'MOZILLA', 'SAMSUNG') OR UPPER(V_out) IS NULL 
                    THEN 0 
                    ELSE -2 
            END 
END

So far I have tried this:

=IF(AND(CU2>800;OR(UPPER(LS2)="GOOGLE";UPPER(LS2)="APPLE"));2;IF(OR(MATCH(UPPER(LS2);{"APPLE";"MOZILLA";"SAMSUNG"};0);UPPER(LS2)=0);0)-2)

And it doesn't seem to work.

Any ideas where I made a mistake?

Thanks!

CodePudding user response：

The MATCH needs a ISNUMBER(...) wrapper:

ISNUMBER(MATCH(UPPER(LS2);{"APPLE";"MOZILLA";"SAMSUNG"};0))

That way it returns a TRUE/FALSE and not a number or error.

and UPPER(LS2)=0 just needs to be LS2=""

and AND(CU2>800;OR(UPPER(LS2)="GOOGLE";UPPER(LS2)="APPLE")) does not quite match the case statement:

OR(UPPER(LS2)="GOOGLE";AND(UPPER(LS2)="APPLE";CU2>800))

is more like the case statement.

The -2 is in the wrong place.

=IF(OR(UPPER(LS2)="GOOGLE";AND(UPPER(LS2)="APPLE";CU2>800));2;IF(OR(ISNUMBER(MATCH(UPPER(LS2);{"APPLE";"MOZILLA";"SAMSUNG"};0));LS2="");0;-2))