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.
* 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

 ABC
1=VLOOKUP("Wheels", B1:C3, 2, FALSE) = 6Bearings8
2=VLOOKUP("Bearings", B1:C3, 2, 0) = 8Bolts11
3=VLOOKUP("Bolts", B1:C3, 1, TRUE) = BoltsWheels6
4=VLOOKUP("NoMatch", B1:C3, 1, FALSE) = #N/A  
5=VLOOKUP("Wheel", B5:C7, 1, TRUE) = BearingsWheels6
6=VLOOKUP("Bolt", B5:C7, 1, TRUE) = #N/ABolts11
7=VLOOKUP("NoMatch", B5:C7, 1, TRUE) = BearingsBearings8
8=VLOOKUP("NoMatch", B5:C7, 2, FALSE) = #N/A  

1 - Return the value in the 2nd column from the "Wheels" row. Exact match. This value is correct.
2 - Return the value in the 2nd column from the "Bearings" row. Exact match. 0 = False. This value is correct.
3 - Return the value in the 1st column from the "Bolts" row. Exact match. This value is correct.
4 - Return the value in the 1st column from the "NoMatch" row. Exact match. There is no match.
5 - Return the value in the 2nd column from the "Wheel" row. Closest match. First column is not sorted. This value is wrong.
6 - Return the value in the 2nd column from the "Bolt" row. Closest match. First column is not sorted.
7 - Return the value in the 2nd column from the "NoMatch" row. Closest match. First column is not sorted. This value is wrong.
8 - Return the value in the 2nd column from the "NoMatch" row. Exact match. There is no match.

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