Hlookup with range of values

**Brahamaham** · 02-07-2011, 04:28 PM

Hi,

I'm trying to do an hlookup based on the attached table.

Problem is that each column is for a range of values. If I do an hlookup without an exact match that works sometimes, but not always. Since the formula picks the closest value, if sometimes picks the upper bound of the wrong range since that is closer to the value than the upperbound of the correct range.

=HLOOKUP(F1,$A$2:$D$3,2)

For example, when F1 is 9,001 I'd like to the formula to return "8", but it returns "7". Any thoughts?

Thanks!!

**NBVC** · 02-07-2011, 04:32 PM

Try removing the dashes in row 1 and converting all of the first 2 rows to numbers...

enter a 0 in an empty cell outside the range and copy it, then select A1:D4 and go to Edit|Paste Special and select Values.

and make the formula:

=HLOOKUP(F1,$A$1:$D$3,3)