Table Array in VLOOKUP Function
The VLOOKUP (vertical lookup) functionVLOOKUP (vertical Lookup) FunctionThe VLOOKUP excel function searches for a particular value and returns a corresponding match based on a unique identifier. A unique identifier is uniquely associated with all the records of the database. For instance, employee ID, student roll number, customer contact number, seller email address, etc., are unique identifiers. read more 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 HLOOKUPHLOOKUPHlookup is a referencing worksheet function that finds and matches the value from a row rather than a column using a reference. Hlookup stands for horizontal lookup, in which we search for data in rows horizontally.read more or VLOOKUP function in Excel, we enter a range of cells as one of the arguments. This range is called the table_array argument.
You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
General Syntax for the VLOOKUP function is as follows:
The VLOOKUP function syntax has the following arguments:
So we can see in the above syntax that the second argument provided to the function is the VLOOKUP table_array.
You can download this VLOOKUP Table Array Excel Template here VLOOKUP Table Array Excel Template
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 the VLOOKUP function as follows:=VLOOKUP(F2,A2:D12,4,1)
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 the 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 the email ID of roll no 6 is correctly extracted and returned with this function.
Now, lets 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 formulaVLOOKUP FormulaThe VLOOKUP excel function searches for a particular value and returns a corresponding match based on a unique identifier. A unique identifier is uniquely associated with all the records of the database. For instance, employee ID, student roll number, customer contact number, seller email address, etc., are unique identifiers. read more in one cell using absolute referencing for table_array and paste it to other cells.=VLOOKUP(F2,$A$2:$D$11,4, 1)
We can see that absolute referencing is createdAbsolute Referencing Is CreatedAbsolute reference in excel is a type of cell reference in which the cells being referred to do not change, as they did in relative reference. By pressing f4, we can create a formula for absolute referencing.read more 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 excels shortcutKeyboard Excels ShortcutAn Excel shortcut is a technique of performing a manual task in a quicker way.read more 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 Ids is correctly extracted and returned with absolute referencing for table_array.
Now, lets say that the 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 the VLOOKUP function includes the sheet name followed by an exclamation mark and cell range.=VLOOKUP(A2,Example1!A2:D12,4, 1)
We can see that the student records table is contained in the range:A2: D12 in the worksheet named Example1, whereas the cell and worksheet where we wish to return the 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 the 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
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
All in One Excel VBA Bundle (35 Courses with Projects)