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 to look for in the first column of the table_array.
table_arrayThe cell range or a range name containing the table of data.
col_index_numThe column number for the value you want returned.
range_lookup(Optional) Whether to find an exact match:
True / 1 = Closest match is returned (table_array must be sorted) (default)
False / 0 = Exact match is returned (table_array does not have to be sorted)

Remarks

* For an illustrated example refer to the page under Lookup Functions.
* XLOOKUP was added in Office 365 to replace this function.
* This function is not case sensitive when searching for text strings.
* This function supports wildcards (? or *).
* Wildcard character: ? = a single character.
* Wildcard character: * = multiple characters.
* This function should be used when you want to lookup a matching value in a particular column of a table and then return a value in the corresponding row.
* 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 a one of these.
* If "lookup_value" is text, then it can also include the two wildcard characters (*) and (?).
* If "lookup_value" is smaller than the smallest value in the first column of "table_array", then #N/A is returned.
* If "lookup_value" cannot be found and "range_lookup" is True, it uses the largest value that is less than the "lookup_value".
* 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" is left blank, then True is used.
* If "range_lookup" = True, then an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than "lookup_value" is returned.
* If "range_lookup" = True, then the values must be sorted into ascending order (-1, 0, 1, A, a, FALSE, TRUE).
* If "range_lookup" = False, then an exact match will be found. If one is not found, then #N/A is returned.
* If "range_lookup" = False, then "table_array" does not have to be sorted.
* What happens if your tables contain empty cells.
* 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 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 MATCH function to return the position of a value.
* You can use the INDEX function to return the intersection of a row and a column.
* You can use the INDEX and MATCH functions as an alternative when your 'lookup value' is not in the first column.
* You can use the OFFSET and MATCH functions 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.office.com

 ABC
1=VLOOKUP("Bearings",B1:C3,2,TRUE) = 8Wheels6
2=VLOOKUP("Bolts",B1:C3,2,TRUE) = 11Bearings8
3=VLOOKUP("Wheels",B1:C3,1) = BoltsBolts11
4=VLOOKUP("Wheels",B1:C4,2) = 11  
5=VLOOKUP("Wheels",B1:C3,1,FALSE) = Wheels  
6=OFFSET(B1,MATCH("Wheels",B1:C3,0),1,1,1) = #N/A  
7=VLOOKUP("Wheels",B2:C4,1,FALSE) = #N/A  

5 - The equivalent using OFFSET and MATCH

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