VLOOKUP
The VLOOKUP function returns a value from a collection of columns by using the left column of values to pick a row and a column number to pick a column in that row.
Tip: For an enhanced search that works in any direction, use XLOOKUP.
VLOOKUP(search-for, columns-range, return-column, close-match)
columns-range: A collection of cells. columns-range must contain a reference to a single range of cells, which may contain any values.
return-column: A number value that specifies the relative column number of the cell from which to return the value. The leftmost column in the collection is column 1.
close-match: An optional modal value that determines whether an exact match is required.
close match (TRUE, 1, or omitted): If there’s no exact match, select the row with the largest left-column value that is less than or equal to the search value. If you use close match, you can’t use wildcards in search-for.
exact match (FALSE or 0): If there’s no exact match, returns an error. If you use exact match, you can use wildcards in search-for. You can use the wildcard ? (question mark) to represent one character, an * (asterisk) to represent multiple characters, and a ~ (tilde) to specify that the following character should be matched rather than used as a wildcard. REGEX can only be used in VLOOKUP if you use exact match.
Notes
VLOOKUP compares a search value to the values in the leftmost column of a specified collection. Unless an exact match is required, the row containing the largest left-column value that is less than or equal to the search value is selected. Then, the value from the specified column in that row is returned by the function. If an exact match is required and none of the leftmost-column values match the search value, the function returns an error.
Examples |
|---|
Given the following table: |
A | B | C | D | |
|---|---|---|---|---|
1 | 0 | A | lorem | 1 |
2 | 20 | E | ipsum | 2 |
3 | 40 | I | dolor | 3 |
4 | 60 | O | sit | 4 |
5 | 80 | U | amet | 5 |
=VLOOKUP(20,A1:D5,2) returns "E", because the value "E" is in the same row as 20 (search-for), in the second column (return-column) within the collection specified (columns-range). =VLOOKUP(39, A1:D5, 2) returns "E", because "E" is in the same row as 20, the closest match that does not exceed the value given (39), in the second column of the collection specified. =VLOOKUP("M", B1:D5, 2) returns "dolor", because "dolor" is in the same row as "I", the closest match that does not exceed the value given ("M"), in the second column of the collection specified (which is column C because the first column specified was B). =VLOOKUP("C", B1:D5, 2) returns "lorem", because "lorem" is in the same row as "A", the closest match that does not exceed the value given ("C"), in the second column of the collection specified. =VLOOKUP("blandit", C1:D5, 2) returns 5, because 5 is in the same column as "amet", which is the last value in the specified ranges. This is the case because "blandit" wasn't found in the second row of the specified collection. =VLOOKUP("C", B1:D5, 3, TRUE) returns 1, because 1 is in the same row as "A", the closest match that does not exceed the value given ("C"), in the third column of the collection specified. =VLOOKUP("C", B1:D5, 3, FALSE) returns an error because the value "C" can’t be found (there is no exact match) and exact match was specified as FALSE (return an error if no exact match). =VLOOKUP("C", B1:D5, 4, TRUE) returns an error, because there is no fourth column in the collection specified (which is only three columns). =VLOOKUP(REGEX("^a.*"), C1:D5, 2, FALSE) returns 5, because 5 is in the same row as "amet," the first word in C1:D5 starting with an "a". |