Lookup and Reference Functions in LibreOffice Calc


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 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


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:


Here’s the working flow and the final result.

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


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 working flow
HLOOKUP working flow
HLOOKUP final result
HLOOKUP final result

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


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.


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.


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.

Join our Telegram channel and stay informed on the move.

Notify of

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

1 Comment
Newest Most Voted
Inline Feedbacks
View all comments

When I have a cell that contains text data that has a parenthesis, the lookup fails. I used this to create subclasses of some stock, in the form “name (variant)”. It strikes me that a text lookup should have no trouble with non-alphabetic characters but I noticed it also doesn’t like the “+” sign or square brackets in my limited testing.

Then I recalled that I had enabled regex lookups and the characters were taken as part of a regex search. Sadly, this option seems to be global and can’t be selected on a file by file basis.

Last edited 1 month ago by Gary