Table Array in VLOOKUP
The VLOOKUP (vertical lookup) function in Excel looks up a piece of information or value from one column of a table array or dataset and extracts and returns some corresponding value or information from another column.
VLOOKUP in Excel is a built-in function and is named so because the formula looks for value and searches it vertically down a specific column. It stops as soon as it finds that value and looks to the right of that value in a column that we specify.
The function needs value or arguments in order to run. On creating an HLOOKUP or VLOOKUP function in Excel, we enter a range of cells as one of the arguments. This range is called the Vlookup table array argument.
General Syntax for the VLOOKUP function is as follows:
The VLOOKUP function syntax has the following arguments:
- Lookup_value: Required, represents the value that we want to look up for in the first column of a table or dataset
- Table_array: Required, represents the dataset or data array that is to be searched
- Col_indexnum: Required, represents the integer specifying the column number of the table_array, that we want to return a value from
- Range_lookup: Optional, represents or defines what the function should return in case it does not find an exact match to the lookup_value. This argument can be set to ‘FALSE; or ‘TRUE’, where ‘TRUE’ indicating an approximate match (i.e use the closest match below the lookup_value in case exact match is not found), and ‘FALSE’ indicating an exact match (i.e it returns an error in case exact match is not found). ‘TRUE’ can also be substituted for ‘1’ and ‘FALSE’ for ‘0’.
So we can see in the above syntax that the second argument provided to the function is the VLOOKUP table array.
Examples of VLOOKUP Table Array in Excel
Below are some examples of VLOOKUP Table Array in Excel.
Let us suppose we have a student records’ table consisting of roll number, name, class and email ID of some students. Now if we wish to get the email ID of a particular student from this database, then we use VLOOKUP function as follows:
In the above formula, the range- A2:D12 is the Vlookup table array.
The third argument with value 4 tells the function to return the value in the same row from the fourth column of the student records’ table. The last argument mentioned as 1 (TRUE) tells the function to return an approximate match (exact match if it exists).
We can see that the VLOOKUP formula looks for the value 6 (as cell F2 contains the value 6) in the left-most column of student records’ table by searching from top to bottom.
As soon as the formula finds the value 6, it goes to the right in the fourth column and extracts the Email ID from it.
So we can see that email ID of roll no 6 is correctly extracted and returned with this function.
Now, let’s say we have two tables: an employee table consisting of Employee ID, Employee Name, Team of Employee and Designation of Employee, and another table consisting of some Employee IDs and we wish to find their corresponding Designation, so we apply the VLOOKUP formula in one cell using absolute referencing for Vlookup table array and paste it to other cells.
We can see that absolute referencing is created by typing a “$” in front of the row and column of a cell reference. This will allow the user to copy the cell reference to other cells while locking the reference point: (starting and ending cells of table array-A2:D11 in this case). A keyboard excel shortcut for creating an Absolute Reference is by pressing the F4 key on the keypad after typing the cell reference.
So now when we copy the VLOOKUP formula from cell G2 and paste it down to three other cells G3, G4, and G5, then only the lookup value (the first argument that has cell reference) changes, and the second argument (table_array) remains the same. This is so because, in G2, we used absolute cell referencing for table_array so that the table range remains fixed or locked.
So we can see that Designation for the corresponding Employee Id’s is correctly extracted and returned with absolute referencing for table_array.
Now, let’s say that the Vlookup table array is present on another worksheet (Example1) in the workbook, and the Roll No and corresponding Email ID we wish to find are on another worksheet (Example3) in the workbook. If this is the case, then the table_array argument in VLOOKUP function includes the sheet name followed by an exclamation mark and cell range.
We can see that the student records’ table is contained in the range:A2:D12 in the worksheet named as ‘Example1’, whereas the cell and worksheet where we wish to return value of Roll No 12 is contained in the worksheet named as ‘Example3’. So in this case, the second argument in VLOOKUP function in cell B2 of worksheet ‘Example3’ contains the sheet name that contains table_array followed by an exclamation mark and cell range.
So we can see that email ID of roll no 12 is correctly extracted and returned even when the Vlookup table array is present on another sheet of the workbook.
Things to Remember about VLOOKUP Table Array in Excel
- The argument: table_array is always the second argument in the LOOKUP function.
- The table_array argument in the LOOKUP function always follows the lookup value.
- The range of cells listed as an argument in the Vlookup table array can use absolute or relative cell references.
- By locking the VLOOKUP from a table array, we can quickly reference a dataset against multiple lookup values.
- The cells in the table_array argument can even be present on another worksheet in the workbook. If this is the case, then the Vlookup table array argument includes the sheet name followed by an exclamation mark and cell range.
- The argument ‘table_array’ provided to the LOOKUP function must be at least as many columns wide as the value of argument ‘col_indexnum’.
- For the VLOOKUP function, the table_array must contain at least two columns of data
This has been a guide to VLOOKUP Table Array. Here we learn how to use the VLOOKUP Table Array in Excel along with practical examples and a downloadable excel template. You may learn more about excel from the following articles –