**A comprehensive article to understand VLOOKUP in LibreOffice Calc spreadsheet program.**

VLOOKUP is a kind of search/lookup function in LibreOffice Calc or any popular spreadsheet program such as Microsoft Excel. It’s an abbreviation of “vertical lookup” and searches vertically, i.e. Top to bottom in a table, hence the name.

In a nutshell, the function searches your key to a list/table and returns a value associated with that matched value.

For example, I have the following data set of employees of an organization. How can I find the email address of a specific ID in this table?

Let’s find out.

Table of Contents

## VLOOKUP in LibreOffice Calc

### Syntax of VLOOKUP

=VLOOKUP(search_key, table_array, column number of table array to be returned, search_type)

search_key: the value you want to search for table_array: where you want to search column number: if found which column of value you want to return search_type: 0=Exact match; 1=Approximate match; default is 1 (approx match).

### Example

In the above example, if I need to find out the email address of `Adrian`

whose ID is `572`

; I should use the following formula:

=VLOOKUP(C6,B11:F16,4,0)

**Explanation**: The above formula searches C6, i.e. 572 in the range B11:F16, and returns `column # 4`

from the range. The match type is a `EXACT`

match. Hence the value is `zero`

.

Here’s a detailed explanation in an image.

Since you understand the basics, let’s learn some important aspects of VLOOKUP.

### An absolute and relative reference in VLOOKUP

In the above example, you can see the range reference is used as `B11:F16`

. This is called a relative reference.

But there is a problem here.

If you search a set of values with relative reference and drag the cell handle to fill up the rest of the rows, then B11:F16 gets incremented. That is, the following search happens with B12:F17.

That’s not good if the `table_array`

is changing for each key value.

So, always use the absolute reference with the $ sign or press F4 to convert it. Finally, the proper formula becomes this.

=VLOOKUP(C6,$B$11:$F$16,4,0)

Learn more about the relative and absolute references in this guide, which I explained in detail.

### VLOOKUP only look at the right

At the backend, the function always searches to the right, starting from the top. So, you should keep this in mind while preparing your data or writing the function. Here’s how you can visualize the general workflow of VLOOKUP.

### The key should always be the first column in the table array

One of the important aspects of VLOOKUP is the key field. The first column of the search table (`table_array`

) should always contain the values of your key field.

For the above example, if your ID field appears after Email or Name, then VLOOKUP will not work.

### Approximate and exact match

The fourth parameter of VLOOKUP is wither `TRUE`

or `FALSE`

. This denotes the following.

- 0 or FALSE: Exact match, i.e. entire string should match and values returned
- 1 or TRUE: Approximate match

Let’s consider the following example, where I am searching for commission % for a range of sales figures.

If I want to find out the commission for the sales amount 550 $? Because 550 is not present in the table!

Here, you need the approximate match, i.e. the final argument as 1.

So, if I put a formula as below, I can get the commission 11% for $550.

=VLOOKUP(C6,C11:D16,2,1)

**Explanation**: VLOOKUP tries to find out whether there is an exact match present for 550. And it’s not present in the table. As a next step, it tries to go a step back and returns the closest match, which is 544. And finally returns the 11%.

**Here are a couple of things you should remember here:**

- When using an approximate match, ensure the key column table_array is sorted.
- The sorting order should be ascending.
- If it’s not sorted, then VLOOKUP may give you a #N/A error or incorrect result.
- This parameter is optional. If you omit this in VLOOKUP, then the default is taken as 1, i.e. approximate match.

So, use this approximate match with caution.

### VLOOKUP only returns the first match

As you can see in the above example, only the first one is returned when there are two values of the approximate match.

This also applies to the exact match as well.

In the first example, if I change the last name of Steve to Patel and try an exact match, what should VLOOKUP return?

=VLOOKUP(C6,$D$11:$E$16,2,0)

As you can see, there are two matches as highlighted below. In this scenario, VLOOKUP returns the **first exact match**.

### Wildcard match in VLOOKUP

VLOOKUP in LibreOffice Calc also allows wildcard search with “*” (Asterix). You can specify the “*” in search_key. When you use a wildcard, remember you should always use the exact match type, i.e. FALSE, as the final argument.

=VLOOKUP("Pate" & "*",$D$11:$E$16,2,0)

So, if you use the above formula, it returns the exact same result as the previous example.

### VLOOKUP and #N/A errors in LibreOffice Calc

If you use this function, for sure, you will run into #N/A error as a result of VLOOKUP. The #N/A means the match is not found.

If you are unable to solve #N/A error, here are some of the reasons you should know.

- The search_key or look-up value is not present in the table (first column of table_array).
- The search_key may be misspelt or may contain extra space. You might want to remove space using trim function.
- Carefully check the search_type to see whether you have specified Exact or Approximate
- The table_array is not correctly mentioned. Be careful whether it is locked with relative reference or abosulte reference.

### Wrapping Up

In this article, you learned how you could easily perform VLOOKUP in LibreOffice Calc cell. It is one of the powerful functions which can do anything if used properly. If you run into an error or have any questions, let me know in the comment box below.

Hi: Is there a function in LibreCalc that returns the LAST occurence in an array of the index, rather than the first, as as the “lookup” functions do?

Thanks!

You can find the last occurrence of a character of a cell value.

Thanks mate.

I am having trouble with a Household Budget file in Excel and opened it with Libre Calc. It is giving me some errors that say Err:508. I am not seeing any missing parentheses and don’t know how to fix this. Can you shed any light on the cause of this?

Share a screenshot of the error and the formula present in the cell.

We’re you able to see what is the solution?

Here is the screen shot

=VLOOKUP($Budget.$A18,#NAME?#all,3,FALSE())

The second parameter in your VLOOKUP “

#NAME?#all”is wrong. You must have changed something in your original file. This parameter must contain a valid rangehi, I have 2 columns of 8k+ names in Calc and I want to identify the names that are common to both columns. Then create a list of names that are common both and another 2 lists that are only in column 1 and not column 2 and visa versa. Any suggestions using libre? Thank you in advance

You can use MATCH function to do that.