VLOOKUP for a range?

[JohnLayne]

Hello all,

Can a vlookup formula be used to find where a value falls in a range?

So the value is -4 and I want it to return "Between 0 and -10" or the value is 35 and I want it to return "Between 30 and 40"

I've attached a sample with some values and the table already built.

Thank you!

[NBVC]

Try:

=VLOOKUP(CEILING(ABS(A2),10)*SIGN(A2),$D$2:$E$10,2,FALSE)

copied down.

If you rearrange table so it is in ascending numerical order from -40 to +40, then you can be more efficient with:

=VLOOKUP(CEILING(ABS(A2),10)*SIGN(A2),$D$2:$E$10,2)

[JeffCPA]

VLOOKUP will find the nearest match as long as you don't use FALSE as the last argument. You've done it right by simply omitting that final argument. However, you do need to make a few formatting/presentation changes:

1. You need to use a fixed range for your lookup range. See how in B3, B4, etc it's looking at the wrong range? In the formula in B2, click on "D2" in the D2:E10 range, hit F4, and do the same on E10. Now copy this new formula down.
2. The lookup range for vlookup (your "TABLE") needs to be in ascending order. So start w/ -40 and go up toward positive numbers.
3. You need to be more consistent in your table values; for the negative number ranges, you have the lower end of the range in column D (eg -40 is your value for "b/t -30 and -40), but then w/ the positive numbers, you switch it up and have the high end of the range in column D. This needs to be consistent for your formula to work the way you want it to.
4. If you want "no change" to be returned when the value is 0 exactly, I think you need to change "0 and -10" and "0 and 10" to "-1 and -10" and "1 and 10". It's kind of a contradiction, because conceptually if the value were zero, you could have three valid results from your table. Technically you have the same issue with every multiple of 10, so if you wanted to you could also change the ranges to 10-19, 20-29, etc., but I'm not sure if you're that concerned with subtleties like this.
5. For the new result of "between 1 and 10", your value in column D should be 1. (10 will be the value for 10-20, per #3 above)

Hope this helps. Also, see attached - I uploaded the version of your file I was playing around with.

EDIT: Just read what NBVC posted while I was typing my response. His is definitely better and doesn't require any modifications to the ranges. Well done sir.

[Fcroft]

Here you go. This should work. I rearranged your table to go from smallest to largest and adjusted the values slightly to work with the VLOOKUP formula. Also, you have to lock your table in the formula ($A$1 instead of A1) so that it will copy down correctly. I also added in logical value of 1 in the VLOOKUP formula to have it look for the closest but without exceeding.