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 the first part we looked at renaming a sheet, entered some random data, formatted cells and started to build the logic of the agenda.
In the second part of this tutorial we wrote some time and ‘if’ formulas.
In the third part of this tutorial we learned about nested if and ‘AND’ function.
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’).
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 \ Colum||A||B||C||D|
|4||=AND($A$1>B4,$A$1<C4)||07:30:00||09:00:00||Breakfast and Children to School|
|5||=AND($A$1>B5,$A$1<C5)||09:00:00||15:00:00||Make a time schedule using LibreOffice!|
And with the values instead of the formulas:
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:
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:
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.
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.