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 the next smallest (default)
False (= 0) Exact match, the first column does not have to be sorted

REMARKS
* XLOOKUP was added in Microsoft 365 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, or a name or reference that refers to one of these.
* 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, then the next smallest value is returned.
* If "lookup_value" is smaller than the smallest value, then #N/A is returned.
* 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
* For the Google documentation refer to support.google.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.

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited Top