Complicated IF()

[Jon von der Heyden]

Why won't this work?

=IF(AND(C1<={100;90;80;70},C1>={91;81;71;61}),{"A";"B";"C";"D"},"X")

Trying to return relevant {"A","B"....}, Many more conditions apply so trying to keep formula legnth down, or avoid huge lookup table.

Regards,
Jon

 

[Hap]

I don't know the answer but you might try the formula watch window to get an idea where the formula breaks down.

 

[NateO]

Hello,

The lookup table will be smaller and more efficient (binary) than your two-pronged, linear conditional. Try the following:

=LOOKUP(C1,{0,"X";61,"D";71,"C";81,"B";91,"A";101,"X"})

 

[Jon von der Heyden]

Cool, thanks Nate, much better now.

Still curious though, why didn't my one work in the first instance?

 

[NateO]

You're welcome.

Sorry, I don't see how it could work. I see what you're going for but Excel can't interpret that, it won't assume/implement an index location of the combinations without your telling Excel to do so.

You'd need to multiply booleans and use the index() of where there's a match of 1 against your return array. Should be a much more intensive calculation as you'd have to evaluate all possible combinations; go with Lookup().