Описание тега vlookup

VLOOKUP is an Excel 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

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 array of results. 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.