Documentation/Calc Functions/VLOOKUP

Function name:
VLOOKUP

Category:
Spreadsheet

Summary:
Performs a vertical lookup on a specified data table. Compares a given search value with the entries in the first column of the data table and, when a match is found, returns the contents of the cell that lies in the same row as the matched value but in a different column.

The matching process can utilize either wildcards or regular expressions. By default, the matching is not case-sensitive, but a case-sensitive match can be carried out when using a regular expression.

If your data is set up horizontally, with comparison values in the first row of the data table, use HLOOKUP instead of VLOOKUP.

Syntax:
VLOOKUP(Search criterion; Array; Index[; Sorted range lookup])

Returns:
Returns the contents of the cell that lies in the same row as the matched cell, but in a different column. The data returned may be numeric or non-numeric.

Arguments:
Search criterion is a text string (in quotation marks), a number, or a reference to a cell containing one of those types. Search criterion is matched against the contents of the cells in the first column of Array in order to determine the row of interest.

Array specifies the range of cells occupied by the data table. This range can be defined by entering the cell reference for the upper left-hand cell, followed by a colon, and then the lower right-hand cell reference (for example, A1:E10). Array may also be specified by passing the name of a named range or database range. It is also possible to pass an inline array as the Array argument.

Index is a positive integer, or a reference to a cell containing that number, that indicates the column containing the data to be returned. The first column has the number 1. Index should not be greater than the number of columns in Array.

Sorted range lookup is a Boolean / logical value (TRUE or FALSE), or a reference to a cell containing that value, that indicates whether data in the first column of Array is sorted correctly (as described in the section below).


 * If Sorted range lookup is set to 0 or FALSE, then the data in the first column of Array is assumed to be unordered and VLOOKUP searches for an exact match.
 * If Sorted range lookup is omitted or set to any value other than 0 or FALSE, then the data in the first column of Array is assumed to be ordered and VLOOKUP can produce both exact and inexact (best-fit) matches.

The following conditions may arise:


 * If Index is non-numeric, then VLOOKUP reports a #VALUE! error.
 * If Index is a non-integer value, then VLOOKUP truncates it to an integer.
 * If, after any truncation, Index is less than 1 or greater than the number of columns contained in Array, VLOOKUP reports an invalid argument error (Err:502).
 * If Sorted range lookup is TRUE, but the cells in the first column of the data table are not ordered as expected, then the result returned by VLOOKUP is unpredictable. In such cases, it is possible that no error will be reported and it may not be obvious that the returned result is meaningless.
 * If Sorted range lookup is FALSE, and no exact match to Search criterion can be found in the data table, then VLOOKUP reports the #N/A (value not available) error.
 * If Sorted range lookup is TRUE, and the Search criterion argument would precede the first cell in the first column of the data table, then VLOOKUP reports the #N/A (value not available) error.

Details specific to VLOOKUP function

 * If Sorted range lookup is FALSE and the first column of the table contains multiple cells with the same content, VLOOKUP will only match the uppermost cell of those sharing common content.


 * If Sorted range lookup is TRUE, the first column of the data table should be sorted with numbers in ascending order appearing before text values in alphabetic order. VLOOKUP calculates where in the first column Search criterion would appear in the sorted order.
 * If there is an exact match between Search criterion and one of the entries in the first column, that row is used to determine the return value.
 * If there is no exact match between Search criterion and any of the entries in the first column, the row above where Search criterion would appear in the first column is used.


 * If regular expressions are enabled in the Formulas Wildcards area of the (or  on macOS) dialog, then VLOOKUP will find exact matches treating Search criterion as a regular expression. This only makes sense if Sorted range lookup is FALSE.


 * If wildcards are enabled in the Formulas Wildcards area of the (or  on macOS) dialog, then VLOOKUP will find exact matches treating Search criterion as a wildcard expression. This only makes sense if Sorted range lookup is FALSE.


 * If the Search criteria = and <> must apply to whole cells option is ticked in the General Calculations area of the (or  on macOS) dialog, then Search criterion must match the whole text in a cell; if not, it can match just part of the text.


 * The Case sensitive option in the General Calculations area of the (or  on macOS) dialog has no effect on the operation of VLOOKUP.

Examples:
The following table is a real-world example of an unsorted table, showing data drawn from the periodic table of the chemical elements. The following examples utilize the above data table. In most cases, the Sorted range lookup argument is set to 0 (FALSE) so that VLOOKUP will look for an exact match in the unsorted table.

The following table shows sales data for a small company. Four rows (headed 1 to 4) show the total sales in each quarter of the year and the name of the top performing salesperson in that quarter. Four rows (headed East, North, South, and West) show the total sales in each region for the year and the name of the top performing salesperson in that region in that year. The data in the first column is sorted in the prescribed order and so the examples that follow do not specify a value for the Sorted range lookup argument, with VLOOKUP applying the default value of TRUE.

The final example is included to show that the Array argument can take the form of an inline array. In this case a comma is used as the array column separator, while a semicolon is used as the array row separator. These array separators can be changed via the Separators section of the (or  on macOS) dialog

Related LibreOffice functions:
HLOOKUP

INDEX

LOOKUP

MATCH

OFFSET

ODF standard:
Section 6.14.12, part 2

Equivalent Excel functions:
VLOOKUP