Documentation/Calc Functions/VLOOKUP
TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate
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 expected to be sorted correctly (as described in the #Additional details: 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 either omitted, or set to TRUE or to any numeric value other than 0, then the data in the first column of Array is assumed to be ordered and VLOOKUP can produce "best-fit" (either exact or inexact) 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.
Additional details:
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 the first column in Array) of those sharing common content.
- If Sorted range lookup is TRUE, the first column of the data table is expected to be sorted, with numbers in ascending order appearing before text values in alphabetic order. In case the first column of the data table is not really sorted as expected, then the result is unpredictable. VLOOKUP calculates where in the first column Search criterion would appear in the (expected) 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 immediately before 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 cellsoption 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. By default, VLOOKUP is not case-sensitive, unless a case-sensitive search is imposed by a specific regular expression.
General information about Calc's regular expressions
 |
Note:
|
- A regular expression is a string of characters defining a pattern of text that is to be matched. More detailed, general background information can be found on Wikipedia’s Regular expression page.
- Regular expressions are widely used in many domains and there are multiple regular expression processors available. Calc utilises the open source Regular Expressions package from the International Components for Unicode (ICU). See their Regular Expressions documentation for further details, including a full definition of the syntax for ICU Regular Expressions.
- In addition, the LibreOffice Help system provides a high-level list of regular expressions.
- Calc’s regular expression engine supports numbered capture groups, which allow sub-ranges within a match to be identified and used within replacement text. Parentheses are used to group components of a regular expression together and create a numbered capture group. To insert a capture group into a replacement text, use the "$n" form, where n is the number of the capture group.
General information about Calc's wildcards
Wildcards are special characters that can be used in search strings passed as arguments to some Calc functions; they can also be used to define search criteria in the Find & Replace dialog. The use of wildcards enables the definition of more advanced search parameters with a single search string.
Calc supports either wildcards or regular expressions as arguments, depending on the current application settings. By default, wildcards are supported instead of regular expressions.
To make sure wildcards are supported, go to ▸ ▸ ▸ and check whether the option Enable wildcards in formulas is selected. Note that you can use this dialog to switch to regular expressions by choosing Enable regular expressions in formulas or choose to support neither wildcards nor regular expressions.
The following table identifies the wildcards that Calc supports.
| Wildcard | Description |
|---|---|
| ? (question mark) | Matches any single character. For example, the search string "b?g" matches "bag" and "beg" but will not match "boog" or "mug". Note that it will not match "bg" either, since "?" must match exactly one character. The "?" wildcard does not correspond to a zero-character match. |
| * (asterisk) | Matches any sequence of characters, including an empty string. For example, the search string "*cast" will match "cast", "forecast", and “outcast”, but will not match "forecaster" using default Calc settings. If the option Search criteria = and <> must apply to whole cells is disabled in ▸ ▸ ▸ , then "forecaster" will be a match using the "*cast" search string.
|
| ~ (tilde) | Escapes the special meaning of a question mark, asterisk, or tilde character that follows immediately after the tilde character. For example, the search string "why~?" matches "why?" but will not match "whys" nor "why~s". |
Wildcard comparisons are not case sensitive, hence "A?" will match both "A1" and "a1".
These wildcards are supported in both Calc and Microsoft Excel. Therefore, if interoperability between the two applications is needed, choose to work with wildcards instead of regular expressions. Conversely, if interoperability is not necessary, consider using regular expressions for more powerful search capabilities.
Examples:
The following table is a real-world example of an unsorted table, showing data drawn from the periodic table of the chemical elements.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Element | Symbol | Atomic Number | Relative Atomic Mass |
| 2 | Hydrogen | H | 1 | 1.008 |
| 3 | Helium | He | 2 | 4.003 |
| 4 | Lithium | Li | 3 | 6.94 |
| 5 | Beryllium | Be | 4 | 9.012 |
| 6 | Boron | B | 5 | 10.81 |
| 7 | Carbon | C | 6 | 12.011 |
| 8 | Nitrogen | N | 7 | 14.007 |
| 9 | Oxygen | O | 8 | 15.999 |
| 10 | Flourine | F | 9 | 18.998 |
| 11 | Neon | Ne | 10 | 20.18 |
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.
| Formula | Description | Returns |
|---|---|---|
| =VLOOKUP("Helium"; $A$2:$D$11; 2; 0) |
The function matches the string "Helium" in the first column of the data table (cell A3) and returns the data from the cell at the intersection of this row and the second column of the table (cell B3). The symbol for helium is returned. |
He |
| =VLOOKUP("CARBON"; $A$2:$D$11; 3; 0) |
The function matches the string "CARBON" in the first column of the data table (cell A7) and returns the data from the cell at the intersection of this row and the third column of the table (cell C7). The atomic number for carbon is returned. |
6 |
| =VLOOKUP("oxygen"; $A$2:$D$11; 4.6; 0) |
The function matches the string "Oxygen" in the first column of the data table (cell A9) and returns the data from the cell at the intersection of this row and the fourth column of the table (cell D9) - the Index value of 4.6 is truncated to 4. The relative atomic mass of oxygen is returned. |
15.999 |
| =VLOOKUP("Neon"; $A$2:$D$11; 2; TRUE()) |
This example shows the confusion that can arise if the Sorted range lookup argument is set or defaulted to TRUE, when the data table is not sorted in the expected order. This formula returns the (unpredictable) text "C", when "Ne" would be expected. |
C |
|
=VLOOKUP("^bo.*"; $A$2:$D$11; 4; 0)
|
The function matches the regular expression "^bo.*" in the first column of the data table (cell A6) and returns the data from the cell at the intersection of this row and the fourth column of the table (cell D6). The relative atomic mass of boron is returned. |
10.81 |
|
=VLOOKUP("nit*"; $A$2:$D$11; 4; 0)
|
The function matches the wildcard expression "nit*" in the first column of the data table (cell A8) and returns the data from the cell at the intersection of this row and the fourth column of the table (cell D8). The relative atomic mass of nitrogen is returned. |
14.007 |
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 E_reg, N_reg, S_reg, and W_reg) 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.
| A | B | C | |
|---|---|---|---|
| 1 | Category | Total Sales | Top Salesperson |
| 2 | 1 | $27,094 | Samuel |
| 3 | 2 | $96,516 | Aaron |
| 4 | 3 | $82,485 | Pippa |
| 5 | 4 | $48,487 | Ollie |
| 6 | E_reg | $99,908 | Lillian |
| 7 | N_reg | $83,535 | George |
| 8 | S_reg | $46,553 | Zara |
| 9 | W_reg | $24,586 | Kristina |
| Formula | Description | Returns |
|---|---|---|
| =VLOOKUP(0; $A$2:$C$9; 2) |
The function tries to match the number 0 in the first column of the data table, but since 0 is less than the first number (1) in the column, the formula reports the #N/A error. |
#N/A |
| =VLOOKUP(2.5; $A$2:$C$9; 2) |
The function tries to match the number 2.5 in the first column of the data table. Since 2.5 is greater than 2 and less than 3, VLOOKUP uses the value 2 (located in row 3). The formula returns the data from the cell at the intersection of this row and the second column of the table (cell B3). The total sales for the second quarter is returned. |
96516 |
| =VLOOKUP(3; $A$2:$C$9; 2) |
The function matches the number 3 in the first column of the data table (cell A4) and returns the data from the cell at the intersection of this row and the second column of the table (cell B4).
The total sales for the third quarter is returned.
Note that the formula |
82485 |
| =VLOOKUP(123; $A$2:$C$9; 2) |
The function tries to match the number 123 in the first column of the data table. Since 123 is greater than the (other) numbers present, VLOOKUP uses row 5. The formula returns the data from the cell at the intersection of this row and the second column of the table (cell B5). The total sales for the fourth quarter is returned. |
48487 |
| =VLOOKUP("C_reg"; $A$2:$C$9; 3) |
The function tries to match the string "C_reg" in the first column of the data table, but since "C_reg" is alphabetically before the first text ("E_reg") in the column, the formula reports the #N/A error. |
#N/A |
| =VLOOKUP("F_reg"; $A$2:$C$9; 3) |
The function tries to match the string "F_reg" in the first column of the data table. Since "F_reg" is alphabetically after "E_reg" but before "N_reg", VLOOKUP uses the former, located in row 6. The formula returns the data from the cell at the intersection of this row and the third column of the table (cell C6). The top salesperson for the "E_reg" region is returned. |
Lillian |
| =VLOOKUP("S_reg"; $A$2:$C$9; 3) |
The function matches the string "S_reg" in the first column of the data table (cell A8) and returns the data from the cell at the intersection of this row and the third column of the table (cell C8). The top salesperson for the "S_reg" region is returned. |
Zara |
| =VLOOKUP("Zagreb"; $A$2:$C$9; 3) |
The function tries to match the string "Zagreb" in the first column of the data table. Since "Zagreb" is alphabetically after the other strings present, VLOOKUP uses the value that is located lowest in the list (row 9). The formula returns the data from the cell at the intersection of this row and the third column of the table (cell C9). The top salesperson for the "W_reg" region is returned. |
Kristina |
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.
| Formula | Description | Returns |
|---|---|---|
| =VLOOKUP(5; {1, "Monday"; 2, "Tuesday"; 3, "Wednesday"; 4, "Thursday"; 5, "Friday"; 6, "Saturday"; 7, "Sunday"}; 2) |
The function matches the number 5 in the first column of the array and returns the data from the cell at the intersection of this row and the second column of the array. |
Friday |
Related LibreOffice functions:
ODF standard:
Related (or similar) Excel functions:
VLOOKUP