HLOOKUP not working? (at least, not the way I thought it would)

I typically use the "False" statement in any lookup so I get exact matches, but today I came across a problem where I need the closest match. I thought setting the HLOOKUP to "TRUE" would give me closest match, but nope...

Here is my array:

Row1: 0 45 27 1
Row2: 2 3 4 5

My hlookup formula is uses the value "28" as my look-up value, and scans the above array to return the value in row 2. Since I had it set to "TRUE" I expected it to return the value of 4 since in Row1 the number 27 is closest to 28.

Nope, I get 5... in fact it consistently seems to return only the last column.

So is there some formula out there that would return the value of 4 in the above example, i.e. be smart enough to find the number in row1 that was closest to 28?

With any of the lookups that are not being exact, you need to have the lookup array ordered properly and it doesn't necessarily do the closest but rather the first larger or the first smaller (i.e. 26 would return you to 1 not 27)

Your array would have to be changed to...

0,1,27,45
2,5,4,3

Then you would get the appropriate response...

Hope that helps...