Questions tagged [vlookup]

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:

1. 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.
2. 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"}).
3. 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.
4. range_lookup - TRUE or FALSE (or their numeric equivalents of 1 or 0) 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:

• VLOOKUP on Office.com
• VLOOKUP in VBA

Issues with VLOOKUP are often from one or more of the following causes:

1. Attempting to “look to the left” – see emboldened text at 1. above. A col_index_num of 0 or less returns #VALUE! and the range for the table_array cannot be reversed within the formula (D10:A1 is treated as A1: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).

2. Seeking a value that is not in the table_array, probably returning #N/A, or Error 2042 in VBA. Formatting (a text ‘1’ is not the same as a numeric 1) 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.

3. 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)).

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

5. Failing to add 0 (or FALSE) as the fourth parameter. range_lookup is optional and defaults to TRUE (or 1) 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.