## What Is Table Array In VLOOKUP Function?

The

VLOOKUPtable array is the second argument in the function. And it is a data range containing a minimum of two columns. The first column is where we can expect to find the lookup value. And a column on the right of the first column within the chosen data range will contain the value we require theVLOOKUP()to return in the target cell.

Users can specify the table array in the **VLOOKUP()** as a relative or absolute excel reference to a dataset in the same or different sheet. And thus, it makes the **VLOOKUP** function resourceful for fetching the corresponding data from within and outside the current sheet.

For example, the first table lists items, their categories and quantities.

If the requirement is to update the category for the item cited in the second table based on the first table data. Then, we can use the **VLOOKUP table array based on cell value **concept to fetch the category data for the specified item, **Oranges**, in the target cell F2.

In the above **VLOOKUP table array based on cell value** example, the second argument in the **VLOOKUP()** is the table array, which is a relative reference to the cell range **A2:B7**.

It implies that the function in the target cell searches the lookup value, **Oranges**, in the cell range A2:B7 in the first column of the specified table array. And once it locates the first occurrence of the lookup value in column A, cell A5, it returns the data in the same row of the last column of the given table array, cell B5 value, **Fruit**.

And thus, the **VLOOKUP() **function return value in the target cell F2 gives the required category information of the item specified in cell E2.

##### Table of contents

###### Key Takeaways

- The
**VLOOKUP**table array is the data range, with a minimum of two columns, which we supply as the second argument value to the function. - Users can supply the appropriate table array to the
**VLOOKUP()**when they must fill one table based on the data provided in another table in the same or different worksheet. - We can supply the
**table_array**argument value to the**VLOOKUP()**as a relative or absolute reference to a cell range in the same or different sheet or as a named range. Otherwise, we can keep it variable using the**INDIRECT()**.

### Examples

Check out the following examples that explain how to provide the table array value to the **VLOOKUP()** to use the function effectively.

#### Example #1

We shall see a **VLOOKUP table array named range** example.

The first table lists employees, their designations and joining dates.

If the requirement is to update the joining date information for the employee specified in cell E2 in the second table based on the data in the first table. And assume the target cell is F2 with the data format set as **Short Date **in the **Number Format** option in the **Home **tab.

Then, we can use the **named range in excel** to make the VLOOKUP function return the required data in the target cell.

**Step 1:**Select the cell range A2:C11 and type**Emp_Data**in the**Name Box**to create a named range.

[Alternatively, we can select the required cell range, click the **Formulas** tab and select the **Define Name **option.

The **New Name **window will open.

Otherwise, we can press **Alt **+ **M **+ **M + D **to access the **New Name **window.

Update the required name for the chosen cell range in the **Name **field in the **New Name **window.

And click **OK** to create the required named range.]

**Step 2:**Choose the target cell F2 and start typing in the**VLOOKUP()**, as explained below.

*=VLOOKUP(*

According to the **VLOOKUP()** syntax, we must enter the values for four arguments. While the first three arguments are compulsory, the fourth one is optional.

So, we will enter the first argument, **lookup_value**, as a reference to the cell containing the specific employee name in the second table, cell E2, followed by a comma.

The second argument is **table_array**. And as we must get the required information based on the first table, we will provide the **table_array** value as the named range, **Emp_Data**. And for that, we can type “**Em**”, and Excel will show the named ranges starting with the specified phrase.

As we have only one named range starting with “**Em**”, we see one option, **Emp_Data**. Double-click it to choose it and enter a comma.

Next, we shall enter the third argument, **col_index_num**, as **3** since we need to display the joining date for the specified employee in the second table based on the source dataset. And the joining date data is available in the third column in the specified **table_array** named range.

And then enter a comma.

Next, once we enter a comma, Excel will show the options for an approximate and exact match as the last argument **range_lookup** value. And as we require an exact match, double-click the **FALSE** option to select it.

Finally, close the bracket.

**Step 3:**Press**Enter**to view the value the**VLOOKUP()**returns in the target cell.

We created the source dataset as a named range, **Emp_Data**.

So, supplying it as the table array to the **VLOOKUP()** enables the function to search for the lookup value, **Tabitha Bush**, in the first column of the source data.

On the other hand, the argument **col_index_num** value is **3**. It implies that the function must return the required value from the third column, column C, counted from the first column in the specified table array.

So, once the function finds its first occurrence in the first column, cell A5, it returns the data in the same row of the column specified by **col_index_num**, cell C5 value, **2/13/2023**.

#### Example #2

The following illustration explains how to use the **INDIRECT excel function** as the **table_array** argument in the **VLOOKUP()** when the requirement is to look up the same value from multiple tables.

The two tables in the image below contain a list of students, their scores and their ranks in Mathematics and Science tests.

And the requirement is to update the ranks of a student, **Ken Carpenter**, in the two tests, based on the first two datasets. Assume the target cell range are D16:D17.

Then, here is how to use an **INDIRECT()**-based variable table array in the **VLOOKUP()** in the target cells to achieve the desired output.

**Step 1: **Choose the target cell D16, enter the **VLOOKUP() **containing the **INDIRECT()**, and press **Enter**.

*=VLOOKUP(A16,INDIRECT(B16),3,0)*

[ Alternatively, select cell D16 and choose the **Formulas** tab → **Lookup & Reference** function group → **VLOOKUP **function.

The **Function Arguments **window opens.

Next, we must update the argument values in the corresponding fields in the **Function Arguments** window.

Finally, clicking **OK** will close the **Function Arguments** window. And we will get the required rank data in the target cell.]

**Step 3:**Use the fill handle to enter the formula in cell D17.

*=VLOOKUP(A17,INDIRECT(B17),3,0)*

The **VLOOKUP() **in cell D16 accepts the reference to the cell containing the student name to lookup, cell **A16**. Next, the **INDIRECT() **accepts the reference to the cell containing the table array range to search for the lookup value, cell **B16**. And it returns the absolute reference of the specified cell range, **$C$3:$E$12**.

Further, as we require the rank data, the **col_index_num** argument value is **3**, indicating column E of the first dataset. And, for an exact match, we set the last optional argument as **0**, which interprets as **FALSE**.

And thus, based on the inputs, the **VLOOKUP()** searches for the specified student’s name in column C cells in the range, C3:C12. And as it locates the value in cell C9, it returns the value in cell E9, **8**, as the student’s rank in the Mathematics test.

Furthermore, step 2 will set the first argument in the function as cell reference A17 and also **update** **VLOOKUP table array** argument value as **INDIRECT(B17)**.

And thus, the function searches the lookup value in the new table array range **$G$3:$G$12**, which the **INDIRECT()** returns. It finds the lookup value in cell G7 and column I is the third column from the first column in the second dataset. So, the function returns the student’s rank in the Science test as the cell I7 value, **9**.

#### Example #3

Let us see a **VLOOKUP table array different sheet** example.

Consider we have two worksheets, **SalesRep_Sales_Commission **and **Sales_CommissionPercentage**.

While the first one contains a list of sales representatives at a firm and their achieved sales targets, the second sheet contains the commissions one can earn for different sales figures.

And we must update the commission the sales representatives earned for their achieved sales targets in column D of the first sheet based on the second worksheet data. Also, the column D data format is **Percentage** in the **Home **tab → **Number Format** option.

Then, applying the **VLOOKUP table array different sheet** method, with an approximate match, will help us update the required values in the target cells.

However, ensure the data in the first column in the table array, referenced from the second sheet, must be in ascending order. Otherwise, the **VLOOKUP()** may return an incorrect value based on an approximate match.

**Step 1:**Choose the first target cell D3 and start typing in the**VLOOKUP()**, as explained below:

*=VLOOKUP(*

Next, we must look up the sales figure value provided in the first sheet in the second worksheet to find an approximate match for obtaining the required commission percentage.

The reason for finding an approximate match is that the commission percentages in the second sheet are for different sales value ranges. For example, if a sales representative achieved a sales target of $11,000. Then, as the value falls between **$10,000 – $15,000**, the commission percentage the representative earns will be **2%**, based on the second sheet data.

So, enter the **lookup_value** argument value, reference to cell **C3**, followed by a comma.

Next, click the second sheet tab and select the cell range C2:D6 to provide the **table_array **argument value, followed by a comma.

The above step will show the sheet name in the **table_array** argument as the reference is to a table array data in a different sheet.

And then, provide the **col_index_num** argument value as **2** since the commission percentage data is in the second column in the specified table array. And enter a comma.

And once we enter a comma, Excel will list the **TRUE** and **FALSE** options to choose the value for the last **range_lookup** argument. And as we aim to get an approximate match, we must double-click on the **TRUE** option to select it.

Finally, close the bracket.

**Step 2:**Press**Enter**to execute the formula in cell D3 in the first sheet.

However, using the fill handle to enter the above formula in the remaining target cells will **update VLOOKUP table array** range, as we provided a relative reference to the array range.

Thus, we must make the table array range reference absolute in the formula to keep it fixed. And for that, we must select the cell range in the **table_array **argument value in the cell D3 formula and press **F4 **to change the reference from relative to absolute.

**Step 3:**Using the fill handle, update the formula in the remaining target cells.

Let us see the cell D12 formula to understand how the table array reference from another sheet works.

The **VLOOKUP()** searches for the lookup value in cell C12 of the first sheet, **$18,400**, in the cell range C2:C6 in the second worksheet. And, as the lookup value is just over **$15,000** and we chose the last argument value as **TRUE**, the **VLOOKUP()** finds an approximate match in cell C3. Thus, it returns the commission percentage in the same row in column D, the cell D3 value, **4.00%**, as the output.

### Important Things To Note

- Ensure the number of columns in the
**VLOOKUP**table arrayequals at least the number supplied as the**col_index_num**argument value. - If the requirement is to use the
**VLOOKUP()**in contiguous cells, with the table array range being the same. Then, lock the table array when supplying it to the**VLOOKUP()**in the first cell. - If the first column in the table array does not contain the lookup value, or the lookup value is below the lowest value in the first column in the lookup range. Then, the
**VLOOKUP()**may not work for the specified table array.

### Frequently Asked Questions (FAQs)

**1. How to lock table array in VLOOKUP?**

We can lock table array in **VLOOKUP **function by using the following methods:**• **Absolute referencing the cell range when supplying it as the **table_array **argument value to the **VLOOKUP()**.**• **Supplying a named range as the **table_array **argument value to the **VLOOKUP()**.**• **Updating the **VLOOKUP()** in the worksheet and password-protecting the sheet.

**2. Why VLOOKUP table array not working?**

The **VLOOKUP **table array is not working for the following reasons, which we shall see with an example.

The table below lists a set of products, their grades and costs.**• The first column of the specified table array does not contain the lookup value.**

For example, the requirement is to update the cost for the product specified in the second table based on the data provided in the first table. And assume the target cell is F1, with the data format set as **Currency** in the **Home** tab → **Number Format** option.

Then, we can use the **VLOOKUP()** in the target cell to obtain the required data, as shown below.

However, the function returns the **#N/A **error. And the reason is the lookup value in cell E1, **Prod_10**, is not present in the first column of the lookup range, specified as the **table_array** argument value.**• The lookup value is below the least value in the table array’s first column.**

For example, the requirement is to update the cost of a **Grade 1** product in cell F6 in the third table based on the data in the first table. And the data format of cell F6 is **Currency**.

Then, using the **VLOOKUP()** in the target cell will fetch us the required data. However, whether it is an approximate or exact match, the function returns the **#N/A** error.

The reason is that the lookup value, **1**, given in cell E6, is below the lowest value in the first column of the specified table array, **2**.**• The table array is not locked.**

For example, the requirement is to update the cost of the two products specified in the fourth table based on the first table data. And assume the target cell range is F10:F11, with the data format set as

**Currency**.

Then, using the

**VLOOKUP()**in the target cells will fetch the required output. And for that, we can enter the

**VLOOKUP()**in cell F10 and then drag the fill handle downwards to update the formula in cell F11.

While the

**VLOOKUP()**returns the correct value in the first target cell F10, its output is the

**#N/A**error in the second target cell F11.

Since we did not use the absolute reference to the table array range, the table array range changed when we used the fill handle to update the formula in cell F11. And as the lookup value is not in the updated lookup range, the function returns the error.

**3. What is the shortcut for table array in VLOOKUP?**

The shortcut for table array in **VLOOKUP** function is to click the first cell in the lookup range and then press the keys **Ctrl **+ **Shift **+ **↓** + **→**.

The shortcut will select the cells down the first column until an empty cell. And then, it will choose the cells in the same rows, as chosen in the first column, in the columns on the right of the first column till the first empty column.

### Download Template

This article must be helpful to understand the **VLOOKUP Table Array**, with its formula and examples. You can download the template here to use it instantly.

### Recommended Articles

This has been a guide to VLOOKUP Table Array. Here we discuss how to use it along with examples & downloadable excel template. You can learn more from the following articles –

## Leave a Reply