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_value | The value to look for in the first column of the table. |
| table_array | The cell range or range name containing the table of data. |
| col_index_num | The column number for the value you want to return. |
| range_lookup | (Optional) A logical value specifying the type of match performed: True / 1 = Closest match, exact or the next smallest (table_array must be sorted in ascending order) (default) False / 0 = Exact match (table_array 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. * 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" = 1 or True, then an approximate match is returned. In other words, if an exact match is not found, then the next smallest value is returned. * If "range_lookup" = 1 or True, then the first column must be sorted into ascending order (-1, 0, 1, A, a, FALSE, TRUE). * 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 XLOOKUP function to return a value in the same row (or column). * 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.microsoft.com * For the Google documentation refer to support.google.com |
|
| 5 - The equivalent using OFFSET and MATCH |
© 2022 Better Solutions Limited. All Rights Reserved. © 2022 Better Solutions Limited Top