Table Lookup (Using Hlookup and Match) not Working Right

[velocityinc]

I have a table from which I would like to extract a value
(corresponding to 7) and its not working correctly with my formula. Im
looking for some assistance in setting it right.

Distance (Col A1) Fare (Col B2)
a2=1-5 b2=3.0
a3=6-10 b3=4.5
a4=11-15 b4=6.5

Cell E5 has 7 for distance and I used
=HLOOKUP(B1,B1:B4,MATCH(E5,A2:A4,0)+1,FALSE) to find the fare and it
does not work.

Please kindly help correct me.

Thanks,
V

 

[T. Valko]

Try this:

...........A..........B
2........0...........3
3........6...........4.5
4......11...........6.5

E5 = 7

=IF(E5="","",VLOOKUP(E5,A2:B4,2))

Result = 4.5

Biff