Thread: Lookup to find where integer falls within a table of starting and ending values

I have a table with three columns A,B,C, the first column is the value I’d like to get returned from the formula I need help with. The second and third column contain starting and ending number ranges. I have searched and played with all kinds of ideas but have not been able to figure out a solution. I can’t use a nested if-then because unfortunately I have over 150 rows with beginning and ending integers and that would not work.

The Problem: If a value falls between one of the range pairs in my table I’d like the lookup formula on a differnt tab to return the value from column A.

Return Value, Starting Number, Ending Number
A- 001-010
B- 855-897
C- 151-156
D- 243-312
E- 313-323
F- 1,224-1,244
Example:
5 with the "=Lookup formula" would return "A", 244 would return "D", 880 would return "B" and so on. I attached an example table with data for better reference. Any help is much appreciated!

This is really nice! I noticed the formula works perfectly for single character values in the "Return value Column" but not if there are 2 or more characters. I mentioned my actual data set has upwards of 150 rows they would each have a distinct label but that quantity would far exceed the 26 letters of the alphabet + numbers 1-9 so if I tried to do some type of secondary look up that paired the two it probably would not work. Any ability to increase the number of characters to two or more?

Hmm? That's a different problem alltogether, I'll look at it again later tonight.

What sort of combination of characters could you have?
Is there any pattern, or are they random?

WOW.... That did it! And after I cleaned up some errors due to my data it worked amazingly well! I tried playing with a variation of the sumproduct but never worked. Your implementation of it is quite slick I will have to study it in a bit more depth to understand it for myself but it absolutely works! Thanks again!