Search code examples
excelfunctionexcel-formulalookup

Why didn't HLOOKUP with an approximate match (true) return accordingly the next largest value that is less than lookup_value

Please help me explain the result from HLOOKUP() function with the logical value = TRUE as the data below

B6: returns 0.35 when choosing a smaller lookup_array

B6: returns 0.35 when choosing a smaller

At B5: I understand the result: firstly, Excel sorted the first row in ascending order, left to right (then we have b c d f g m); secondly, chose the next largest value that is less than lookup_value g; hence f was picked and the result was 0.6 accordingly.

The explanation is correct in B7, but not correct in B6. At B6, the table_array is narrower, but everything is the same.

My questions are:

  1. What is the logic of return at B6? I even changed B1 to d, the result at B6 was 0.3 accordingly.

  2. Does HLOOKUP() see from the last column of the array (right to left), and no sort? If yes, when does this rule work because it is contradicted B5 above?

Please see the picture below. B18 returns 0.32. Look like the rule is first picked from the right to left, and smaller than the lookup_value z

But why B19 and B20 return #N/A?

B19 and B20 return #N/A. B18 returns 0.32 instead of 0.25 as the rule at B5

If I change the first cell in the first array to b (see cell H14 in the below picture). B19 and B20 (I19 and I20 in the below picture) now return 0.25

B19 and B20 don't return #N/A if the first cell of the lookup_value is 'b' instead of 'u'

Additional information: I found that HLOOKUP() will find the last value (from right to left) in the array that has the same format as the lookup_value. I attached an image to show it as cell C31 and I31.

But the unexplained result is I33 as mentioned in question 2

find in the same format part to the lookup_value

I changed values in the lookup_array to see the result in every change, and tried to see the logic and what repeated this time and the last time. I even asked ChatGPT 4.0 but it didn't work, answers are nonsense.

update 2: I used the function in question 1 (first image) in a new spreadsheet in Excel, in spreadsheet in Google Drive and OneDrive as well. B5 was the same return. B6 returned the same. But B7 is 0.3 in Onedrive and 0.35 in Google Drive.

Onedive GoogleDrive

Solution

  • I found the explaination here, it makes sense to me: https://www.experts-exchange.com/articles/2637/Six-Reasons-Why-Your-VLOOKUP-or-HLOOKUP-Formula-Does-Not-Work.html

    The HLOOKUP(), VLOOKUP() may return an error or incorrect value. To solve it, if we omit or use TRUE for range_lookup, we must sort the first column/row