What Is the VLOOKUP Index in a Column?
Microsoft Excel's VLOOKUP function searches for a value in a data column and returns corresponding data from a specified position. This function is useful for dynamically extracting information from a field that receives user input. The VLOOKUP function relies on three mandatory components – the lookup value, table array and column index number – and a fourth, optional component: range lookup.
-
Lookup Value
-
The lookup value is the value you are searching for in the first column of the table array. If the lookup value is smaller than the smallest value in the array's first column, Excel returns a "#N/A" error. The lookup value can be a cell reference, named cell, quoted text string or numerical value.
Table Array
-
The table array references the range of cells that make up the data table. This reference may directly reference the cells, such as "A1:D10," or use a defined-name reference for a group of cells, such as "table" (without quotes). The first column of this reference should contain the data in which you are searching for the lookup value.
Column Index Number
-
The column index number is the position of the data you want returned. Specifically, "1" refers to the first column in the table array, "2" refers to the second column in the table array and so on. This is true regardless of where the table actually resides. If the table array is located in cells V20:X30, the first column is V, the second column is W, and the third column is X. The numerical index tells Excel to return the value in one of these columns.
Range Lookup
-
The range lookup tells Excel to look for an exact or approximate match. If the range lookup is omitted, its value is assumed to be "TRUE," in which case Excel searches for an exact match or the closest value that is still less than the lookup value. If the range lookup is set to "FALSE," Excel will look for an exact match; if no exact match is found, a "#N/A" error is returned.
Putting It All Together
-
Assembling the VLOOKUP components properly ensures that the function works as expected. Each component should be separated by a comma and be arranged in the following order: lookup value, table array, column index number and range lookup. As an example, you might use the formula "=VLOOKUP(A1,C1:F10,2,FALSE)" to tell Excel to look in column C for the exact value in cell A1 and return the corresponding data in column D.
-
