Excel formmula to determine which range of value a certain number falls between

[subby]

Hi All,

I need to figure out how to avoid nested IF-s because I have to determine if the value of cell A1 falls between one of the 20-ish ranges of values, and and return a value based on the result

So the cell A1 can contain any integer between 0 and 375
the ranges for which I have to determine if A1's value falls into are (inclusive): 0-14, 15-29, 30-44, 45-59, 60-74, 75-89, 90-104, 105-119, 120-134, 135-149, 150-164, 165-179, 180-194, 195-209, 210-224, 225-239, 240-254, 255-269, 270-284, 285-299

For example if A1 is 229, then the result should be the cell that is below the one that says "225-239", which is 17 500.

Can somebody please suggest a fromula OR VBA solution for me?

 

[RoryA]

It would be easier if you could alter the values in row 1 to reflect the start value for each range. Then a simple LOOKUP formula would suffice.

Otherwise you'll need something like:

=LOOKUP(A2,--LEFT($E$1:$W$1,FIND("-",$E$1:$W$1)-1),E2:W2)

 

[subby]

It would be easier if you could alter the values in row 1 to reflect the start value for each range. Then a simple LOOKUP formula would suffice.

Otherwise you'll need something like:

=LOOKUP(A2,--LEFT($E$1:$W$1,FIND("-",$E$1:$W$1)-1),E2:W2)

Hi RoryA,

Thank you very much!

This works perfectly!!

Justt out of curiosity, how would the LOOKUP work if only the starting value of the range were in row 1? (see the attached picture)

Thank you for your answear!

Subby

 

[RoryA]

It would then just be:

=LOOKUP(A2,$E$1:$W$1,E2:W2)

 

[subby]

It would then just be:

=LOOKUP(A2,$E$1:$W$1,E2:W2)

Thank you!

You solved my dilemma completely.

Subby