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

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.

edit retag close merge delete

Sort by » oldest newest most voted

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.

more