VLOOKUP is a Spreadsheet function for retrieving a value from an array using a vertical lookup value and a positive column offset. Use with [excel-formula] or [google-sheets-formula] or any other product that supports such lookup
VLOOKUP is a Spreadsheet function for retrieving a value from a table array using a lookup value and a column offset.
There are four arguments to the VLOOKUP function:
- lookup_value - the value being searched for. This value must be located in the leftmost column of the table array in order to return a result.
- table_array - a range or array of values being searched for the lookup value. This argument can be a range (ex:
A1:D10) or a text array (ex:{"ABC","DEF"}). - col_index_num - a number setting the column of the array from which the corresponding value to be returned from the corresponding row that the lookup value is obtained from.
- range_lookup -
TRUEorFALSE(or their numeric equivalents of1or0) respectively) indicating whether the lookup is for an exact match, or closest value to the lookup value sorted in ascending order. This argument can also be a cell reference (which may contain a formula) which returns one of the above values.
It may be used on the worksheet as well as in vba by calling the Application.WorksheetFunction.VLookup Function or just Application.VLookup.
Related Tags:
excel-formula
worksheet-function
google-sheets-formula
Links:
Issues with VLOOKUP are often from one or more of the following causes:
Attempting to “look to the left” – see emboldened text at 1. above. A col_index_num of
0or less returns#VALUE!and the range for the table_array cannot be reversed within the formula (D10:A1is treated asA1:D10). Solutions here may be to rearrange the columns (by either copying, ie a ‘helper column’, or moving the lookup_value column to the left) or to apply the INDEX/MATCH combination instead (example).Seeking a value that is not in the table_array, probably returning
#N/A, orError 2042in VBA. Formatting (a text ‘1’ is not the same as a numeric1) and trailing spaces, for example, may give the appearance that a lookup_value exists in the table_array when it does not. A simple way to check for this is to copy the search value onto the position in the table_array where a match is expected.Seeking a value that is outside the table_array range. This can happen when a VLOOKUP formula is copied down that refers to a stacked (columnar) range to be searched that is not an entire column and that has not been anchored (set with
$s preceding row numbers) or defined as a Named Range. (example (for COUNTIFS)).Applying the wrong col_index_num, which is one-based. With a table_array, of say a column of numbers on the left and colour names immediately to the right, to return a colour name based on a lookup_value that is a number then the relevant col_index_num is
2.Failing to add
0(orFALSE) as the fourth parameter. range_lookup is optional and defaults toTRUE(or1) if not specified. This makes the assumption that the table_array is sorted in ascending order and applies a binary search for speed and efficiency. Where the fourth parameter for VLOOKUP is not specified and the table_array is not sorted in ascending order an incorrect result, but often plausible in appearance, is very probable.
VLOOKUP does not provide an all results of lookup in a array. If, for example, various shades of red were all assigned the same numeric code in the table_array a numeric lookup_value would, at most, find only one instance (the first, say ‘pink’). Sometimes this may be resolved to find a single result (but say ‘scarlet’ instead of ‘pink’) with a concatenated field in the table_array, and creation of a ‘key’ to suit. (example). Another case where this approach may suit is if there are several different people with the same surname - so the key might be to combine surname with a first name.
