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 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
Explanation: The formula search for ID at
"200" in table
F6:G9, and returns the second column (“2”) of the selected table. Here’s a mockup of the explanation.
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.
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.
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
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.
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.