Lookup() vs. Index() of Match()

[sondrak]

I have a spreadsheet in LibreOffice Calc 6.1.2.1. I have a column which had this formula:

=LOOKUP(D2,$J2:$Q2,$J$1:$Q$1)

It gave appropriate answers for about 2/3 of the cells, but gave #N/A for the remaining 3rd.
I replaced the column with this formula:

=INDEX($J$1:$Q$1,1,MATCH(D2,$J2:$Q2,0))

That gave me just what I wanted. However, I still don’t understand why these formulas are different. Shouldn’t they have the same results? Does this have something to do with sorting? Documentation for the lookup function here didn’t help me.

[JohnSUN]

Yes, you are right, this is due to the sorting.

The third parameter in the MATCH() function indicates in which order the data is located in the search range. You specified 0. This means, “search until found, find the exact match.”

In LOOKUP() function, you have no such possibility, you just have to make sure that the data is sorted in advance. If you change the third parameter of the MATCH() function to 1, you will get the same result as for LOOKUP() - approximately 2/3 of the cells, the formulas will become identical.

[newbie-02]

depending of the ‘amount’ of answer you need you (OP) may also look for vlookup an hlookup, they have the search criteria in the formula, but it’s written somewhere that a combination of index and match is less ressource consuming, thus you might be at the optimal solution

[fritsje25]

And VLOOKUP seems to have an error where if the searched match appears multiple times, it will return #NA.
For example, if you are looking to match ‘123’ and VLOOKUP finds both ‘123’ and ‘11234’ it will return #NA because it found ‘123’ multiple times.
In other words: it does not do an exact match search, or maybe it does, but there is a bug.

So when you use VLOOKUP and find that you have this issue, you need to go use INDEX and MATCH, thus suggesting VLOOKUP is not not always a good idea. See also here: Vlookup wrong results