OpenOffice is an open source package of office applications like Writer (equivalent to MS word), Calc (equivalent to MS excel), Impress (equivalent to MS power point), etc.
You can also perform vlookup in Open Office Calc which gives you the same result that you get in Excel. The way you write vlookup in Calc is not very different from the one you write in Excel. This guide will help you to understand the syntax and will give you an example for understanding the concept the better.
Calc Vlookup Syntax
Below is the syntax of vlookup in Open Office Calc,
=VLOOKUP(SearchCriterion; Array; Index; SortOrder)
Let me quickly explain you what each parameter is used for.
- SearchCriterion – SearchCriterion as the name indicates is the search criteria or the value for which you would like to perform a search.
- Array – This is the target data source against which you would like to perform a search or we can also say that the SearchCriterion will be searched against this data. You need to provide at least two columns for the Array parameter.
- Index – This is the column in which the value you require is available (in the array) should be specified in this Index column.
- SortOrder– This is an optional parameter. Enter 0 or FALSE if the first column in the Array (against which you are performing the search) is not sorted.
Note: Please note that unlike Excel, OpenOffice uses ; (semicolon) to separate the parameters. So if you manually enter the vlookup formula please ensure that you use semicolon or else you might get an error.
Calc Vlookup Example
Let us quickly look into a simple example that will make things more clear,
We are going to use the same example that we used for the Excel Vlookup. I have two sheets in my Calc spreadsheet,
- Employee Details
- Pay Structure
In “Employee Details” sheet I have the following columns Emp ID, Employee Name, Designation and Salary. All the columns have values already filled in except Salary column and this is the column for which we are going to perform a vlookup.
The other sheet “Pay Structure” has Designation and Salary columns. The Salary is predefined based on the Designation column.
Goal: Since most organizations have thousands of employees it is not possible to manually refer the designation and populate the salary. So, our goal is to populate the Salary column in Employee Details sheet.
What you require:
You just require the below two pre-requisites to complete the vlookup successfully.
- Please note that you require the Open Office already installed in your machine.
- You can download the Example sheet from here and just follow the below instructions to get the desired result.
Steps to Perform Vlookup in OpenOffice
Open the vlookup-in-openoffice.ods spreadsheet and follow the below steps to complete the vlookup.
Go to the Employee Details sheet and place your cursor on the D2 cell (Salary Column Row2) and navigate to the Insert Menu and click on the Function…
You can also invoke this Function window using CTRL + F2 shortcut
In the function wizard select Spreadsheet in the Category drop-down and select VLOOKUP in the function list. Click Next
Now the vlookup function will show you the list of parameters for which you have to provide the values. Enter and mentioned below.
Search criterion: Enter C2 or directly select the select the C2 cell in the Employee Details sheet.
Array: Place your cursor in the Array text area and then go to the Pay Structure sheet and select cells from A2 to B7.
Index: Place your cursor in the Index text area and enter 2. We have entered 2 because the 2nd column (Salary) in the Pay Structure sheet has the value that we need.
As mentioned earlier the sort order parameter is optional. Now click OK.
STEP 4: The cell D2 should now have the correct salary for the Project Manager Designation. Now you would like to perform the same for the rest of the cells in column D.
All you have to do is drag the cell D2 all the way down till D11. But before dragging the cell formula, place your cursor in cell D2 and in the formula bar enter $ symbol before the array cell references.
Your formula should now look like this,
The reason why we use $ symbol before the array references is to avoid the change of cell references.
You can also watch the below demo that shows what we have discussed above.