Excel Formula for If Statement to Decide Status in Leveling Condition

Question

i've been trying to get status in cell based on reference value A3:A. The reference value compare to another column with several level. But it seems my logic seems a bit off as if i put reference value a number, the result in B3:B is either give a result or give #value result. I've attached the note problem and link to the google doc.

Any clue or solution will be great. Thank you

here's my formula =if(and($A5<=C5,$A5>D5),C$1,if(and($A5<=D5,$A5>E5),D$1,if(and($A5<=E5,$A5>F5),E$1,if(and($A5<=F5,$A5>G5),F$1,if(and($A5<=G5,$A5>=H5),G$1,if(and($A5<H5,$A5>=I5),H$1,if(and($A5<I5,$A5>J5),I$1,if(and($A5<=J5,$A5>K5),J$1,if(and($A5<=K5,$A5>L5),K$1,if(and($A5<=L5,$A5>M5),L$1,if(and($A5<=M5,$A5>N5),M$1,if(and($A5<=N5,$A5>=O5),N$1,if(and($A5<O5,$A5>=P5),O$1,if(and($A5<P5,$A5>AC5),P$1,if(and($A5>=Q5,$A5<R5),Q$1,if(and($A5>=R5,$A5<S5),R$1,if(and($A5>=S5,$A5<T5),S$1,if(and($A5>=T5,$A5<U5),T$1,if(and($A5>=U5,$A5<V5),U$1,if(and($A5>=V5,$A5<W5),V$1,if(and($A5<=AC5),AC$1,if(and($A5>=W5,$A5<X5),W$1,if(and($A5>=X5,$A5<Y5),X$1,if(and($A5>=Y5,$A5<Z5),Y$1,if(and($A5>=Z5,$A5<AA5),Z$1,if(and($A5>=AA5,$A5<AB5),AA$1,if($A5>=AB5,AB$1,"")))))))))))))))))))))))))))

Note

i expect the formula can be optimized so it will give a status for any reference value , not a #value.

If any modification or better suggestion arise, it will be great.

Thank you

Welcome to SO. Like this it is very hard to understand your requirement, it would help, if you provide some sample data and your expected output. Reading How to Ask and minimal reproducible example will help you to improve your question. Maybe this link will help you too: Stack Overflow question checklist. — Ike
– Ike, Commented Nov 9, 2023 at 8:55
I do lots of things with if() and my first point is that any more than 3 levels then you should change the approach. A lookup table might be better here - both for control of how it works and testing as well as future editing. — Solar Mike
– Solar Mike, Commented Nov 9, 2023 at 9:01
Can't you find your data using the Match() worksheet function (but instead of "Exact match", use a "smaller than" or "larger than" option)? — Dominique
– Dominique, Commented Nov 9, 2023 at 9:53
thank you @Ike , i've been sharing google doc link as references for my question above in the body. — Aldrian Rahman Pradana
– Aldrian Rahman Pradana, Commented Nov 9, 2023 at 12:31
Well, i've been very fond of index and match. But just can't arrange how to use match as question. Might be good if any example approach given to try. Thank you — Aldrian Rahman Pradana
– Aldrian Rahman Pradana, Commented Nov 9, 2023 at 12:33

Solar Mike · Accepted Answer · 2023-11-09 09:05:34Z

3

So, I show a different approach, which I consider to be much easier to control and edit that many nested if() statements:

answered Nov 9, 2023 at 9:05

Solar Mike

8,4255 gold badges23 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Aldrian Rahman Pradana Over a year ago

Hi @Solar Mike , that's awesome insight and it didn't come to my mind at first. So the vlookup itself will match to the nearest number or just previous number position? Other things on my question is, is it applicable to a situation where there's duplicate number in below row that gives a result too?

Solar Mike Over a year ago

So you can replace the false with true for an exact match, worth playing with these functions and their arguments. You should have little trouble since you are so familiar with index() and match() as you say in your comment - match uses 1,0 and -1 for the sorting arguments.

Aldrian Rahman Pradana Over a year ago

I've tried it and it works. It gives me better approach on this one. Thank you

Collectives™ on Stack Overflow

Excel Formula for If Statement to Decide Status in Leveling Condition

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related