Hlookup with range of values

[cancansova]

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!!

 

[Cubist]

There's no attached table.

 

[cancansova]

Hi, I'm trying to do an hàm 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!!

sorry everyone because I haven't attached the table yet

 

[Alex Blakenburg]

With the attached table I am sure @Cubist would have you sorted already but if I had to guess, it would be that your column values aren't sorted in ascending order, is that the case ?
HLookup (and VLookup) need the data to be sorted to use approximate match. XLookup does not and since you have MS 365 see if the below works for you.

=XLOOKUP(F1,$A$2:$D$2,$A$3:$D$3,"",-1)