How to edit VLOOKUP table array

Table Array in VLOOKUP Function

In VLOOKUP or vertical lookup when we use a reference cell or value to search in a group of columns containing data to be matched and retrieve the output, the group of range we used to match is called as VLOOKUP table_array, in table array the referenced cell is at the leftmost side of the column.

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
For eg:
Source: VLOOKUP Table Array (wallstreetmojo.com)

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 the exact match is not found), and FALSE indicating an exact match (i.e., it returns an error in case the 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

You can download this VLOOKUP Table Array Excel Template here VLOOKUP Table Array Excel Template

Example #1

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.

Example #2

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.

Example #3

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

  • The argument: table_array is always the second argument in the LOOKUP function in excelLOOKUP Function In ExcelThe LOOKUP excel function searches a value in a range (single row or single column) and returns a corresponding match from the same position of another range (single row or single column). The corresponding match is a piece of information associated with the value being searched. read more.
  • The table_array argument in the LOOKUP function always follows the lookup value.
  • The range of cells listed as an argument in the 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

  • VLOOKUP Formula for Text Values
  • VLookup Formula with Match
  • VLOOKUP Tutorial in Excel
  • VLOOKUP from Another Workbook
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>