Formula needed

[Lemmesee]

I habe a table like this:
Columns B - C are ranges between numbers.

A B C
1 15 1 5
2 9 6 10
3 19 11 21
4 20 22 30

On another sheet I need to find the range that a number is in and return
it's corresponding Value from column A
On this another sheet, If A1=1 to 5 B1 will =15
If A1=6 to 10 B1 will =9
etc....
I was trying to do it wit INDEX,MACTH,MATCH . I cant get it to work because
the second MATCH would need to have Column C in descending order.
I hope this makes sense
And thanks in advance for any help.

 

[Jacob Skaria]

Try the below in Sheet2
=IF(AND(A1>=1,A1<=30),LOOKUP(A1,Sheet1!B1:B4,Sheet1!A1:A4),"")

If this post helps click Yes

 

[Joel]

If you index is always a multiple of 5 (1 to 5, 6 to 10, 11 to 15) then use
Mod 5 as your index into the table. You may need to offset by 1

Use Mod(A1 - 1,5) where A5 is your input
A1 Output
1 to 5 0
6 to 10 1
11 to 15 2
16 to 20 3
21 to 25 4
26 to 30 5

 

[Lemmesee]

I must have made a bad example.
I have a sheet similar to my example but it has a lot more rows and the
ranges between B & C vary but, of course, a number would be only in 1 range.
I need to lookup a value like "5" in B:C . I need to return the value
from the same row, but, in col A

 

[Ron Rosenfeld]

I habe a table like this:
Columns B - C are ranges between numbers.

A B C
1 15 1 5
2 9 6 10
3 19 11 21
4 20 22 30

On another sheet I need to find the range that a number is in and return
it's corresponding Value from column A
On this another sheet, If A1=1 to 5 B1 will =15
If A1=6 to 10 B1 will =9
etc....
I was trying to do it wit INDEX,MACTH,MATCH . I cant get it to work because
the second MATCH would need to have Column C in descending order.
I hope this makes sense
And thanks in advance for any help.

If your ranges are contiguous, and column B is in ascending order, as they are
in your example, then:

Sheet2!B1: =LOOKUP(A1,Sheet1!B1:B4,Sheet1!A1:A4)

The only "upper bound" test you would need would be for the ultimate upper
bound, so, in general, something like:

=IF(A1>MAX(Sheet1!C:C),"Out of Bounds",LOOKUP(A1,Sheet1!B1:B4,Sheet1!A1:A4))

--ron