How to lookup a value in a 2 col table and find the nearest match?

[Chats]

I hope somebody can help.

I have a two column table in an Excel worksheet:

[tt]

Row LowVal HighVal
1 10 50
2 51 75
3 76 100

etc

[/tt]

I have another list of values on another sheet in the same workbook like this:

[tt]

25
58
61
etc
[/tt]

I need some way to look up each of the values in the second sheet (25,58,61 etc) and find out which row in sheet1 matches where the value is >=LowVal and <= HighVal (so the value 25 would return Row1 because it is between LowVal=10 and HighVal=50.

Is there any way to do this with Excel formulas?

Thanks

Adrian

 

[GlennUK]

VLOOKUP will find the correct number for you, but to translate that into a row number you need to put it inside another function ....

=MATCH(VLOOKUP(A1,Sheet2!$A$1:$B$4,1),Sheet2!$A$2:$A$4,0)

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.