/

Lookup and Reference Functions in LibreOffice Calc

1224 views

A simple beginner’s guide to lookup and reference functions in LibreOffice Calc.

LibreOffice Calc provides all the necessary lookup and reference functions for your day-to-day workflow and solving complex spreadsheet problems.

This article is a summary of all the necessary functions.

Table of contents

Lookup and Reference in LibreOffice Calc

The lookup concept basically is to retrieve or find some values based on the “key” or reference. For example, if you have two sets of data with a common column, say “ID” of fruits as below. One table has the name and per kilo price. And another table has a subset of the fruits.

How can you get the names and calculate the total price by fetching relevant data from the other table?

Vlookup base example
Vlookup base example

VLOOKUP

VLOOKUP is an abbreviation of vertical lookup. So, to find out the names and other items in the above example, I will add the below formula to cell B2 using VLOOKUP. Here’s its syntax and formula.

Syntax of VLOOKUP

=VLOOKUP(search item, table array, column number of table array to be returned, search type)
search type: 0=Exact match; 1=Approximate match

Example formula

=VLOOKUP(A2,F6:G9,2,0)

Explanation: The formula search for ID at A2, i.e. "200" in table F6:G9, and returns the second column (“2”) of the selected table. Here’s a mockup of the explanation.

VLOOKUP in action
VLOOKUP in action

To calculate further, you can also fetch the price from the target table, put it in column D, and multiply the total quantity to calculate the price.

Then drag the cell handle down to fill up the rest of the cells with the formula.

Also, make sure to change the cell reference as global by adding “$” to prevent it from incrementing, which changes the search table. This is called an absolute reference. You can achieve it via pressing F4 while keeping the cursor in the target cell.

So, the final formula becomes this:

=VLOOKUP(A2,$F$6:$G$9,2,0)
=VLOOKUP(A2,$F$6:$H$9,3,0)*C2

Here’s the working flow and the final result.

Vlookup working flow
Vlookup working flow
VLOOKUP final result
VLOOKUP final result

HLOOKUP

The next function is the horizontal lookup (or HLOOKUP). The HLOOKUP one is exactly opposite to VLOOKUP explained above.

So, this function searches left to right instead of searching top to bottom. That said, here’s the result of the exact same example above.

=HLOOKUP(A2,$G$5:$J$6,2,0)
=HLOOKUP(A2,$G$5:$J$7,3,0)*C2
HLOOKUP working flow
HLOOKUP working flow
HLOOKUP final result
HLOOKUP final result

As you can see, the results are precisely the same.

INDEX and MATCH

The beauty of Calc is you can get the above same result using INDEX and MATCH functions. You do not need the lookup functions.

Here’s the syntax of both functions.

=INDEX(reference, row, column,[range])
reference: Table/range from where the data would be picked up
row: Row number of the above reference table
column: Column number of the reference table
=MATCH(search criteria, lookup array, type)
search criteria: the value to be searched
lookup array: single dimension array for search (e.g. one column)
type: 0=exact match 1=approximate match

So, to get the same result as above, you can use the two following formulas.

=INDEX($F$6:$G$9,MATCH(A2,$F$6:$F$9,0),2)

Explanation: Return the value from table F6 to G9. The row number is found by MATCH(), which is 2. And the column number is entered as 2 since we need to return the value from the G column.

=INDEX($F$6:$H$9,MATCH(A2,$F$6:$F$9,0),3)*C2

Explanation: Similarly, applying the same concept can return the price by mentioning the column number as 3.

INDEX working flow
INDEX working flow
INDEX and MATCH final result
INDEX and MATCH final result

Wrapping Up

This guide taught you the basics of various lookup reference functions in LibreOffice Calc. All the demonstrated functions above return the same result. It’s up to you which one you want to use for your use case.

Do let me know in the comment box below if it helps or if you may have any questions.


Looking for something else?

Read our complete tutorial index of Calc, Writer, Impress and Draw.


Join our Telegram channel and stay informed on the move.


arindam

Creator of libreofficehelp.com. The aim is to help as many people with easy-to-understand tutorials. Connect with me via Telegram, Twitter, LinkedIn, or send us an email.

Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x