Complicated if value is between formula

[alexokay]

Hello everyone

I have a question.

I can't wrap my head around how I should make a formula for the following thing I need.

So basically I need to check if a value in a cell is in any of these ranges, I just don't know how to put so many ranges in one formula.
if it's between the two numbers it needs to give the value next to it, if it's over 305 it needs to say "NOT OKAY"

the ranges & values:

0 - 20 / 170

21 - 40 / 196

41 - 70 / 212

71 - 100 / 227

101 - 140 / 253

141 - 180 / 263

181 - 230 / 279

231 - 270 / 304

271 - 305 / 320

305 - ...... / NOT OKAY

My first try was this(M7 being the value needed to be checked and the cells being the values):
=IF(M7=AND((M7>=0),(M7<=20)),J5,IF(M7=AND((M7>=21),(M7<=40)),J6,IF(M7=AND((M7>=41),(M7<=70)),J7,IF(M7=AND((M7>=71),(M7<=100)),J8,IF(M7=AND((M7>=101),(M7<=140)),J9,IF(M7=AND((M7>=141),(M7<=180)),J10,IF(M7=AND((M7>=181),(M7<=230)),J11,IF(M7=AND((M7>231),(M7<=270)),J12,IF(M7=AND((M7>=271),(M7<=305)),J13,IF(M7>305,M7*J14))))))))))

but this one and other adjusted ones didn't work out for me.

If anyone has a simpler working method.

Please let me know!

Thanks in advance.
Alex

 

[snjpverma]

Perhaps, you just need a small Lookup function. Have a look at it and if it doesn't work out, please let us know.

 

[Legacy 456129]

Use =IFS instead where the If and answer go in pairs, up to 127 of them.

=IFS(M7=AND((M7>=0),(M7<=20)), J5, M7=AND((M7>=21),(M7<=40)), J6, M7=AND((M7>=41),(M7<=70)), etc etc

The first matching pair will come out as the answer. Start by entering =IFS and hit the Fx button next to it to get an easy input screen.

But, what I would do is use a combination of Index and Match, where Index(Answer range, Match(Cell M7, range of step values, 1)) which looks up the position in say column A and Index then goes off and find the value you want in column B. All you will be needing is the starts of each range in 1 column. Match than looks at the nearest smaller value, so 60 falls in the range 41-70. Index then can look up the matching multiplier or divisor.
A B 0 170 21 196 41 212 71 227 101 253 141 263 181 279 etc etc

Thus =Index(BRange, Match(M7, ARange,1)). This should pop out 212 for a value between 41 and 70.

Yup tested, when there's an exact match, with 71 you get 227

 

[Legacy 456129]

Every picture tells a story. The advantage of Index/Match is that it does not matter if the lookup data is left or right of the searched data. It's found to be superior in performance over the Lookup variants.

 

[alexokay]

Use =IFS instead where the If and answer go in pairs, up to 127 of them.

=IFS(M7=AND((M7>=0),(M7<=20)), J5, M7=AND((M7>=21),(M7<=40)), J6, M7=AND((M7>=41),(M7<=70)), etc etc

The first matching pair will come out as the answer. Start by entering =IFS and hit the Fx button next to it to get an easy input screen.

But, what I would do is use a combination of Index and Match, where Index(Answer range, Match(Cell M7, range of step values, 1)) which looks up the position in say column A and Index then goes off and find the value you want in column B. All you will be needing is the starts of each range in 1 column. Match than looks at the nearest smaller value, so 60 falls in the range 41-70. Index then can look up the matching multiplier or divisor.
A B 0 170 21 196 41 212 71 227 101 253 141 263 181 279 etc etc

Thus =Index(BRange, Match(M7, ARange,1)). This should pop out 212 for a value between 41 and 70.

Yup tested, when there's an exact match, with 71 you get 227

Oh thank you so much, I'll try it out and see if I understand it completely!

this will help me a lot, much appreciated!

 

[Legacy 456129]

And then you stopped reading, for the full and simplest solution is presented in the picture in my second reply.

Enjoy