Return Value on Third Column if Lookup value is between the range of Values from First and Second Columns

[phyxe]

Hi everyone! I have been wandering around google about how to go about my problem, basically i have this kind of data

A	B	C
78.7	79.99	Red
77.4	78.69	Blue
..	..	..
0	3.11	Color

this data will go down in fixed interval til it hits zero

I turned this table into a named ranged for easier referencing. Now here's my problem: on another sheet (say sheet2) i have rows that contains values that may fall between the value of row A and B. I need to return the value in column C if the look up value is between the value of Column A and B. I tried to do ifs vlookup match and index but i can't seem to find a work around, I know that if i use AND it will turn into a very long nested if formula. Is there a possible way to do it in an easier less messy formula? Thank you for your responses in advance

 

[etaf]

=LOOKUP(2,1/(Sheet2!A:A<=B2)/(Sheet2!A:A>=A2),Sheet2!C:C)

 

[phyxe]

=LOOKUP(2,1/(Sheet2!A:A<=B2)/(Sheet2!A:A>=A2),Sheet2!C:C)

thanks for the fast reply, but i'm quite confuse on how to use your formula

 

[etaf]

see if this helps

Dropbox - File Deleted - Simplify your life

www.dropbox.com

I need to return the value in column C if the look up value is between the value of Column A and B
Now here's my problem: on another sheet (say sheet2) i have rows that contains values that may fall between the value of row A and B.

Sheet2!A:A is a specific number , a value that may fall between the value of A & B

I need to return the value in column C if the look up value is between the value of Column A and B.

Sheet2!C:C is the colour RED,BLUE etc

Taken from your example

B2 is the Range max
A2 is the range min

 

[phyxe]

see if this helps

Dropbox - File Deleted - Simplify your life

www.dropbox.com

I need to return the value in column C if the look up value is between the value of Column A and B
Now here's my problem: on another sheet (say sheet2) i have rows that contains values that may fall between the value of row A and B.

Sheet2!A:A is a specific number , a value that may fall between the value of A & B

I need to return the value in column C if the look up value is between the value of Column A and B.

Sheet2!C:C is the colour RED,BLUE etc

Taken from your example

B2 is the Range max
A2 is the range min

Thank you so much!!! This will quicken my work by a lot

 

[etaf]

you are welcome