What is a shortcut for a nested if greater than function in excel?

Question

=IF(Z2>=CU25,CV25,IF(Z2>=CU24,CV24,IF(Z2>=CU23,CV23,IF(Z2>=CU22,CV22,IF(Z2>=CU21,CV21,IF(Z2>=CU20,CV20,IF(Z2>=CU19,CV19,IF(Z2>=CU18,CV18,IF(Z2>=CU17,CV17,IF(Z2>=CU16,CV16,IF(Z2>=CU15,CV15,IF(Z2>=CU14,CV14,IF(Z2>=CU13,CV13,IF(Z2>=CU12,CV12,IF(Z2>=CU11,CV11,IF(Z2>=CU10,CV10,IF(Z2>=CU9,CV9,IF(Z2>=CU8,CV8,IF(Z2>=CU7,CV7,IF(Z2>=CU6,CV6,IF(Z2>=CU5,CV5,IF(Z2>=CU4,CV4,CV3))))))))))))))))))))))

Is there a better way to write this function in excel? The processing is slow because there are so many if functions nested. Basically I am comparing Z2 to values in CU to put it into groups defined in CV.

An example of your data would be helpful, along with an explanation of what you have, and an example of what you're trying to accomplish. As-is, I'm afraid your question doesn't make much sense. It's seems kind of like you're looking for the "last" value in a column. — ashleedawg, Jun 04 '18 at 21:28

danny117 · Answer 1 · 2018-06-05T13:17:32.757

0

Nested greater than lookup in excel

=VLOOKUP(Z2,CU8:CV25,2,1)

edited Jun 05 '18 at 13:17

answered Jun 04 '18 at 21:33

danny117

5,581
1
26
35

No vlookup is verticle. I actually made a sheet with a lookup to simulate the vc25 vc24 vc23 .. the OP was using. – danny117 Jun 05 '18 at 01:41

score 0 · Answer 2 · answered Jun 04 '18 at 23:10

This will accomplish the same thing, but it will evaluate slower since it will compare Z2 to all of C4:C25 instead of stopping when one of the conditions returns TRUE (enter as an array formula, ctrl+shift+enter)

=IFERROR(INDEX(CV4:CV25,MAX(IF(Z2>=CU4:CU25,ROW(CU4:CU25)))-ROW(CU4)+1),CV3)

What is a shortcut for a nested if greater than function in excel?

2 Answers2