[Solved] Check for range?

[Robindude]

I want to check the value of a cell against a range of values. I can do this with nested IF statements (I think, haven't actually tested it yet), but that's so... ugly! I'm hoping for a better method. Currently what I have is this:
IF(I3<V$1;5;IF(I3<W$1;4;IF(I3<X$1;3;IF(I3<Y$1;2;IF(I3<Z$1;1;-10)))))
Effectively this determines which range the value is in (which one it's under first), and then returns the associated value.

[Villeroy]

=LOOKUP(I3 ; $V$1:$AA$2)

with V2:AA2
5 4 3 2 1 -10

This simple formula implies that V1:AA2 is sorted in ascending order and it matches including the threshold values.
IF(I3<=V$1;5;IF(I3<=W$1;4;IF(I3<=X$1;3;IF(I3<=Y$1;2;IF(I3<Z$1;1;-10)))))

[RusselB]

My first impression is that this seems an ideal situation for one of the LOOKUP functions. MATCH and HLOOKUP (based on the order you presented in code) would be my suggestions to check.
Usage of either one of these functions may require a reorganization of the data on your spreadsheet.

[Robindude]

LOOKUP won't work, since I don't want the values of V1:AA2, I want values 5 to -10 depending on the less-than.
That is, V1:AA2 is 5 10 15 26 36

So if I3 is between 10 and 14, the new cell should read 3.
And my idea didn't work for some reason!

[Zizi64]

LOOKUP won't work, since I don't want the values of V1:AA2, I want values 5 to -10 depending on the less-than.
That is, V1:AA2 is 5 10 15 26 36

So if I3 is between 10 and 14, the new cell should read 3.
And my idea didn't work for some reason!

Please upload your real, ODF type sample file here.

[Robindude]

File included. The one I'm trying to make better is cell V3. I got is working (with $ symbols before the letters, too). It works, but it's ugly. Part of the reason I want it this way is so I can fiddle with values V2:Z2 and adjust things.

[MrProgrammer]

I got is working (with $ symbols before the letters, too). It works, but it's ugly. Part of the reason I want it this way is so I can fiddle with values V2:Z2 and adjust things.

Compare formulas in B5 and B6. This depends on C2:G2 being in ascending order, as shown by the values in your example.

201709092121.ods

(8.2 KiB) Downloaded 112 times

[Tutorial] VLOOKUP questions and answers

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.