1

I am currently trying to learn some basic functions in Excel, and have created the following basic spreadsheet to work with:

spreadsheet

I have then attempted to use the HLOOKUP function, using the following arguments:

HLOOKUP("Germany", B1:F4, 4)

For some reason, this returns the value 8 - which is incorrect. However, setting the lookup value to any other country does return the correct value.

Why is this?

4

Your lookup data (first row in data range) is NOT sorted.

You search the EXACT value.

So you MUST use 4th parameter of HLOOKUP function with FALSE value:

=HLOOKUP("Germany", B1:F4, 4, FALSE)

Or sort data columns by 1st row values ascending.

why does it work for all countries if I change the second parameter to A1:F4?

I think that because HLOOKUP without 4th parameter assumes the data is sorted, it uses half-division method to search the result.

Now look: the search range is B1:F1, 5 cells. The middle is D1. It is lower than value to be searched, so the function decreases search range till D1:F1. Its middle is E1 and it is higher than value to search, the function decreases search range till D1:E1. Now search range is 2 cells, and the search result assumed to be D1. So we have wrong result.

When we alter the data range, the search range is A1:F1, 6 cells. The middle is C1. It is equal to the value to be searched. Search stopped. So we have correct result.

It is not correct, it is similar only. For example, create test table, where A1:H1 is a,b,b,b,b,b,b,c and A2:H2 is 1,2,3,4,5,6,7,8. Use HLOOKUP('b',A1:H2,2). The result will be 7.

3
  • Thanks for your response. However, why does this function work for all other countries? And why does it work for all countries if I change the second parameter to A1:F4?
    – M Smith
    Aug 2 '18 at 10:20
  • @MSmith You must ask Microsoft programmers. I don't know. As an experiment - replace "Germany" literal with C1 direct reference...
    – Akina
    Aug 2 '18 at 10:28
  • This helped me a lot today. Should of course have read what the optional variables did more thoroughly. Was ripping my hair. Thanks to this, I didn't go bald! :)
    – Skillzore
    Jun 24 at 14:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.