# Documentation/Calc Functions/VLOOKUP

TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate

## Function name:

VLOOKUP

## Category:

Spreadsheet

## Summary:

Vertical search with reference to adjacent cells to the right. This function checks if a specific value is contained in the first column of an array. The function then returns the value in the same row of the column named by Index.

## Syntax:

=VLOOKUP(**SearchCriterion**; **Array**; **Index**[; **SortedRangeLookup**])

## Returns:

Returns the value of the indicated in the same row of the column named by **Index** by looking for **SearchCriterion** in the first column of the **Array**.

## Arguments:

**SearchCriterion** is the value or a reference to the cell containing that value which is searched for in the first column of the array. If none is found the largest entry less than **SearchCriterion** is taken as a match. From a sequence of identical values <= **SearchCriterion** the last entry is taken.

**Array** is the reference, which is to comprise at least as many columns as the number passed in the **Index**.

**Index** is the number of the column in the array or a reference to the cell containing that number that contains the value to be returned. The first column has the number 1.

**SortedRangeLookup** is a logical value (TRUE/1 or FALSE/0) or a reference to the cell containing that value indicates whether the first column in the array contains range boundaries instead of plain values. In this mode, the lookup returns the value in the row with first column having value equal to or less than SearchCriterion. E.g., it could contain dates when some tax value had been changed, and so the values represent starting dates of a period when a specific tax value was effective. Thus, searching for a date that is absent in the first array column, but falls between some existing boundary dates, would give the lower of them, allowing to find out the data being effective to the searched date. Enter the Boolean value FALSE or zero if the first column is not a range boundary list. When this parameter is TRUE or not given, the first column in the array **must be sorted in ascending order**. Sorted columns can be searched much faster and the function always returns a value, even if the search value was not matched exactly, if it is greater than the lowest value of the sorted list. In unsorted lists, the search value must be matched exactly. Otherwise the function will return #N/A with message: **Error: Value Not Available**.

If **Array** is not sorted, the result is undetermined and implementation-dependent. In most cases it will be arbitrary and just plain wrong due to binary search algorithms.

If its value is FALSE and **SearchCriterion** is text then wildcard characters like question mark (?) (matches any single character) and asterisk (*) (matches any sequence of characters) are allowed for **SearchCriterion**. Tilde (~) must be added before the character in case you want to find the question mark or asterisk character itself.

See Help page entitled Handling of Empty Cells.

- If there is no data less than or equal to
**SearchCriterion**, then the function returns an #N/A Error. If**SearchCriterion**is of type Text and the value found is of type Number, the #N/A Error is returned.

## Additional details:

- Sorted ascending includes smaller Text values before larger ones (e.g., "A" before "B", and "B" before "BA"), and False before True.
- If the types are mixed, Numbers are sorted before Text, and Text before Logicals. Evaluators without a separate Logical type may include a Logical as a Number.
- The values returned may vary depending upon the HOST-USE-REGULAR-EXPRESSIONS or HOST-USE-WILDCARDS or HOST-SEARCH-CRITERIA-MUST-APPLY-TO-WHOLE-CELL properties. You can know more about it in Host-Defined Behaviors.
- The search supports wildcards or regular expressions. With regular expressions enabled, you can enter "all.*", for example to find the first location of "all" followed by any characters. If you want to search for a text that is also a regular expression, you must either precede every character with a "\" character, or enclose the text into \Q...\E. You can switch the automatic evaluation of wildcards or regular expression on and off in
**Tools - Options - LibreOffice Calc - Calculate**.

## Examples:

A | B | C | D | |
---|---|---|---|---|

1 | Avery | Hazel | Jack | William |

2 | Carter | Grayson | James | Scarlett |

3 | Charlie | Harper | Lily | Jackson |

4 | Ella | Julian | Lillian | Mason |

Formula | Description | Returns |
---|---|---|

=VLOOKUP("Charlie";A1:E4;3;FALSE) | The function looks up for "Charlie" in first column (A) and gives the value corresponding to column 3 (C) without using SortedRangeLookup within the same row. |
Lily |

=VLOOKUP("Charlie";A1:E4;3;TRUE) | The function looks up for "Charlie" in first column (A) and gives the value corresponding to column 3 (C) using SortedRangeLookup within the same row. |
Lily |

=VLOOKUP("B",A1:E4,3,TRUE) | The function looks up as "B" in first column (A) and gives the value from column 3 (C) using SortedRangeLookup within the same column. Here we don't have an exact match for "B" so the largest value in column 1 that is less than "B" is used which will be "Avery" and the corresponding value from column C is returned. |
Jack |

=VLOOKUP("B",A1:E4,3,FALSE) | The function looks up as "B" in first column (A) and gives the value from column 3 (C) without using SortedRangeLookup within the same column. Here we don't have an exact match for "B" in Column 1 so the function returns an error value. |
#N/A |

=VLOOKUP(5, {1,"a",2;"b","c",3;"d","e","f";"j","k",4;7,6,5}, 2,1) where there are five rows of values in the array constant, each row separated by a semicolon (;) | The function looks up the number 5 in the first column array, and returns the value from colum 2 in the same row. Here we don't have exact match but we are using SortedRangeLookup so 1 in Column 1 is just less than 5 so corresponding value from column 2 is returned. |
a |

=VLOOKUP(0,{1,"a",2 ; "b","c",3 ; "d","e","f" ; "j","k",4 ; 7,6,5},5,1) | Here SearchCriterion has no value smaller then itself so the function returns #N/A. |
#N/A |

- Entering a nonexistent number/text displays the text with the next number down. To prevent this, enter FALSE as the last parameter in the formula so that an error message is generated when a nonexistent number is entered.

## Related LibreOffice functions:

## ODF standard:

## Equivalent Excel functions:

VLOOKUP