VLOOKUP

VLOOKUP(lookup_value, table_array, col_index_num [,range_lookup])

Returns the value in the same row after finding a matching value in the first column.

lookup_valueThe value you want to find in the first column of the table.
table_arrayThe cell range or range name containing the table of data.
col_index_numThe column number for the value you want to return.
range_lookup(Optional) A logical value indicating the type of match:
True (<> 0) Closest match, exact or closest value less than it (default)
False (= 0) Exact match, the first column does not have to be sorted

REMARKS
* XLOOKUP was added in Excel 2021 to replace this function.
* For an illustrated example refer to the page under Lookup Functions
* This function is not case sensitive when searching for text strings.
* This function supports wildcards (? and *).
* Wildcard character: ? = a single character.
* Wildcard character: * = multiple characters.
* To include the actual wildcard characters use a tilde prefix (~?), (~*) and (~~).
* This function cannot return values that are to the left of the lookup column.
* The 'lookup_value' can be a number, text, logical value, named range or cell reference.
* If 'lookup_value' cannot be found and 'range_lookup' is False, then #N/A is returned.
* If 'lookup_value' cannot be found and 'range_lookup' is True, the value used by this function can be confusing.
* If 'lookup_value' is less than the smallest value, then #N/A is returned.
* If 'lookup_value' is between the smallest and largest values, the closest value less than it is used.
* If 'lookup_value' is greater than the largest value, then the largest value is used (which is the closest value less than it).
* If 'table_array' contains duplicate values, then the results are based on the first match found.
* If 'col_index_num' = 1, the first column value in 'table_array' is used.
* If 'col_index_num' = 2, the second column value in 'table_array' is used.
* If 'col_index_num' < 1, then #VALUE! is returned.
* If 'col_index_num' > the number of columns in 'table_array', then #REF! is returned.
* If 'col_index_num' is not numeric, then #VALUE! is returned.
* If 'range_lookup' = True then the first column must be sorted into ascending order (-1, 0, 1, A, a, FALSE, TRUE).
* This function will only return values less than 256 characters long. If the matching value is more than 255 characters, then #N/A is returned.
* You can use the XMATCH function to return the position of a value.
* You can use the HLOOKUP function to return a value in the same column.
* You can use the LOOKUP function to return a value in a row (or column) that matches a value in a column (or row).
* You can use the INDEX function to return the intersection of a row and a column.
* You can use the MATCH function to return the position of a value.
* You can use the INDEX and MATCH combination as an alternative when your 'lookup value' is not in the first column.
* You can use the IFNA function to return the value or something else if it evaluates to #N/A.
* This function has been available since Excel 1997.
* VBA - The WorksheetFunction.VLookUp does not work properly in all versions. To avoid the errors use Application.VLookUp
* For the Microsoft documentation refer to support.microsoft.com

 ABCD
1=VLOOKUP("Tokyo", B1:C3, 2, FALSE) = 6Paris8 
2=VLOOKUP("Paris", B1:C3, 2, FALSE) = 8Rome11 
3=VLOOKUP("Rome", B1:C3, 2, FALSE) = 11Tokyo6 
4=VLOOKUP("NoMatch", B1:C3, 2, FALSE) = #N/A   
5=VLOOKUP("Rom", B1:C3, 1, TRUE) = Paris Tokyo6
6=VLOOKUP("Romx", B1:C3, 1, TRUE) = Rome Rome11
7=VLOOKUP("Tok", B1:C3, 1, TRUE) = Rome Paris8
8=VLOOKUP("Par", B1:C3, 1, TRUE) = #N/A   
9=VLOOKUP("Toky", C5:D7, 1, TRUE) = Paris   
10=VLOOKUP("Rom", C5:D7, 1, TRUE) = #N/A   
11=VLOOKUP("Wrong", C5:D7, 1, TRUE) = Paris   

1 - Return the value in the 2nd column of "B1:C3" from the row that matches "Tokyo" in the 1st column.
2 - Return the value in the 2nd column of "B1:C3" from the row that matches "Paris" in the 1st column.
3 - Return the value in the 2nd column of "B1:C3" from the row that matches "Rome" in the 1st column.
4 - Return the value in the 2nd column of "B1:C3" from the row that matches "NoMatch" in the 1st column. There is no match.
5 - Return the value in the 1st column of "B1:C3" from the row that has the closest match to "Rom" in the 1st column.
The 1st column is sorted. The value above "Rom" is "Paris".
6 - Return the value in the 1st column of "B1:C3" from the row that has the closest match to "Romx" in the 1st column.
The 1st column is sorted. The value above "Romx" is "Paris".
7 - Return the value in the 1st column of "B1:C3"from the row that has the closest match to "Tok" in the 1st column.
The 1st column is sorted. The value above "Tok" is "Rome".
8 - Return the value in the 1st column of "B1:C3" from the row that has the closest match to "Par" in the 1st column.
The value "Par" is smaller than the smallest value.
9 - Return the value in the 1st column of "C5:D7" from the row that has the closest match to "Toky" in the 1st column.
Incorrect. The 1st column is not sorted. The value "Toky" is smaller than the smallest value, so returning "Paris" is very confusing.
10 - Return the value in the 1st column of "C5:D7" from the row that has the closest match to "Rom" in the 1st column.
Incorrect. The 1st column is not sorted. The value above "Rom" is "Tokyo" so returning #N/A is very confusing.
11 - Return the value in the 1st column of "C5:D7" from the row that has the closest match to "Wrong" in the 1st column.
Incorrect. The value "Wrong" is larger than the last row so returning "Paris" seems to be correct which is very confusing.
© 2025 Better Solutions Limited. All Rights Reserved. © 2025 Better Solutions Limited Top