Documentation/Calc Functions/VLOOKUP

From The Document Foundation Wiki
Jump to navigation Jump to search


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.
Gnome-dialog-warning.svg

Warning:
When using functions where one or more arguments are search criteria strings that represents a regular expression, the first attempt is to convert the string criteria to numbers. For example, ".0" will convert to 0.0 and so on. If successful, the match will not be a regular expression match but a numeric match. However, when switching to a locale where the decimal separator is not the dot makes the regular expression conversion work. To force the evaluation of the regular expression instead of a numeric expression, use some expression that can not be misread as numeric, such as ".[0]" or ".\0" or "(?i).0".


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:

INDEX

HLOOKUP

LOOKUP

MATCH

OFFSET

ODF standard:

Section 6.14.12, part 2

Equivalent Excel functions:

VLOOKUP