Ask Your Question
0

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

asked 2018-12-13 16:05:49 +0200

sondrak gravatar image

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 flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2018-12-13 16:44:13 +0200

JohnSUN gravatar image

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.

edit flag offensive delete link more

Comments

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

newbie-02 gravatar imagenewbie-02 ( 2019-06-02 11:50:39 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-12-13 16:05:49 +0200

Seen: 1,203 times

Last updated: Dec 13 '18