LibreOffice Tutorial – Part 4 – function VLOOKUP

This is the 4th part of my LibreOffice Tutorial to learn about simple useful formulas and a tiny bit of macro to make a simple daily agenda.

In this part I am going to tell you about a very useful function called VLOOKUP.

Last time I picked my favourite formula among 4 different formulas able to compare the current time with a starting time and an ending time.
This is the table I have in my sheet ‘planner’ (with the formulas instead of the values -> Menu ‘View’->’Show Formula’).

Other parts:
In Part 1 we look at renaming a sheet, entering  some random data, formatting cells and starting to build the logic of the agenda.

In Part 2 of this tutorial we write some time functions and IF formulas.

In Part 3 of this tutorial we learn about nested IF and AND function.

In Part 4 of this tutorial we learn about VLOOKUP.

In Part 5 we look at how to create and use a ‘named range’.

In Part 6, we learn the INDEX function.

In Part 7, we learn how to code a (very) simple LibreOffice Macro.

In Part 8, we use the MROUND function to round the time.

In Part 9 part we do some debugging and learned about the ISERROR and NOT functions.

In Part 10 part we add some color and formatting to wrap-up this series.

You may note that I added a new line for activities in the evening to be able to test the formulas as I am writing this article just before going to bed!)

Row \ ColumABCD
1=TIME(HOUR(NOW()),MINUTE(NOW()),0)StartEndsActivity
2=AND($A$1>B2,$A$1<C2)00:00:0006:30:00Zzzzzzz
3=AND($A$1>B3,$A$1<C3)06:30:0007:30:00Wake up
4=AND($A$1>B4,$A$1<C4)07:30:0009:00:00Breakfast and Children to School
5=AND($A$1>B5,$A$1<C5)09:00:0015:00:00Make a time schedule using LibreOffice!
6=AND($A$1>B6,$A$1<C6)15:00:0021:00:00Kids
7=AND($A$1>B7,$A$1<C7)21:00:0022:00:00House Chores
8=AND($A$1>B8,$A$1<C8)22:00:0023:00:00Finally free!
9=AND($A$1>B9,$A$1<C9)23:00:0024:00:00Zzzzzzz
with formulas

And with the values instead of the formulas:

with values

So far we worked on the back-end of our agenda. It means the part of the agenda that we don’t see, but that contains the data and the sort of engine for your agenda.

This time we are going to work a little bit on the front-end of our agenda, in the ‘Now!’ sheet. This will be the part that you look at and that will display the activities matching the current time, the activity coming next, as well as the activity that was just before now.

So in the ‘Now!’ tab, pick any cell in the middle, it doesn’t matter what exact cell it is because it is a ‘receiving-data’ cell and we always can tweak or choose another one later.

I have chosen C8.

In that cell (C8 for me), we are going to use a very useful function called VLOOKUP.

VLOOKUP is also very well known in the world of Microsoft Excel formulas. It means Vertical Lookup, and it is use to search data associated with a ‘key’ value.

VLOOKUP does look for a key value to get data your are looking for bound to that key in a table.

In this function you need to specify the key, the reference of the table and the column number of the data you are looking for and a lookup option for the accuracy of the search.

So in our example we are going to look for the activity that is bound to the key ‘TRUE’, we can use either the word ‘TRUE’ or the value 1.

The table we are going to look at in sheet ‘planner’ is A2:D9. The reference should then be:

$planner.A2:D9

We want to get the current activity from the Activity column, so the column we want to retrieve is the column number 4 of the table.

The option to use can be left blank.

The formula in C8 is therefore:

=VLOOKUP(1,$planner.A2:D9,4,)

Whaouw it displays Zzzzzzz for me that means  it is bedtime…

(Sorry I am using the same excuse that in last article again…)

Just before stopping here, you should know that instead of typing the formula you can select it from the ‘function wizzard’. It’s a small button on the left of the ‘formula bar’. It is a really useful tool when you don’t know how to use a particular formula or when you don’t know what formula to use. VLOOKUP is in the Spreadsheet category.

the function wizzard button

Another tip: instead of typing $planner.A2:D9 in the formula, a common way is to use the mouse select the ‘planner’ sheet and select the entire table from cell A2 to D9, then press the comma key ‘,‘ on your keyboard to continue typing the rest of the formula.

In the next episode of this tutorial series I will be showing you a easy way to reference a table of data using the named range feature in LibreOffice.

If you like this tutorial you could buy me a coffee to help me continue writing add-free tutorials.

Thank you!

Leave a Comment

Your email address will not be published. Required fields are marked *