The **Table Array **is one of the required arguments in the **VLOOKUP **function in Microsoft Excel. The **VLOOKUP **function searches for a certain value within a range (named as ‘**Table Array**’ by MS Excel) to return a value from a specified column in the same row. This article will explain what a **Table Array** is and how it functions in the VLOOKUP with suitable examples and proper illustrations.

**Download Practice Book**

You can download the free Excel template from here and practice on your own.

**Introduction to Table Array Argument in VLOOKUP Function**

The** Table Array** argument in the Excel **VLOOKUP** function is used to find and look up the desired values in the form of an array in the table. While using **the ****VLOOKUP function**, we need to set a data range where we’ll look up our value. This range is called the **Table Array**.

**Syntax:**

**VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])****Arguments:**

**lookup_value**: The value used to look up.

**table_array**: The selected range in which you want to find the lookup value and the return value. It is also called the Table Array.

**col_index_num**: The number of the column within your selected range that contains the return value.

**[range_lookup]**: FALSE or 0 for an exact match, TRUE or 1 for an approximate match.

**3 Examples Using the Table Array in VLOOKUP**

Let’s get introduced to our dataset first. I have used some products’ names, codes, and prices in my dataset. Now I’ll show three easy examples to use Table Array with simple steps.

**Example 1: Regular Table Array in the Same Excel Worksheet**

In this example, we’ll use just a single table array in the **VLOOKUP **function. Here, we’ll look up the value Shirt which is in **Cell B13, **and extract its corresponding price.

**Steps:**

⏩ Type the formula given below in **Cell C13**:

`=VLOOKUP(B13,B5:D11,3,FALSE)`

Here, **B5:B13** is the **Table Array**.

⏩ Then just hit the **Enter **button to get the output.

Now you will see that we have got our expected result.

**Example 2: Regular Table Array from Another Excel Worksheet**

Let’s see if our data range locates in another worksheet and we want to get the return for the lookup value then what to do. It’s very simple, I just will place the data table in a worksheet named **‘Table’**. And then, I’ll find the output in another worksheet named ‘**From Another Sheet**’. See the following steps carefully.

**Steps:**

⏩ At first type the part of the formula-

`=VLOOKUP(B4,`

⏩ Then **click** on the worksheet name where our data range is located. It will take you on that worksheet.

⏩ Now **select** the data range **B5:D11 **from this sheet.

⏩ Later, complete the other arguments input like the previous example.

⏩ Finally, hit the **Enter **button and drag the** Fill Handle** icon.

Here’s our output for three items.

**Example 3: Variable Table Array**

Suppose there are multiple tables in our sheet and we want to look up the same item in those tables then we’ll use **the INDIRECT function** with the **VLOOKUP **function to do it. The **INDIRECT **function returns a reference to a range. For that, I have made two tables having the same items but different codes and prices. Now, I’ll look up the item ‘Watch’ on both tables.

**Steps:**

⏩ Activate **Cell H5**.

⏩ Type the formula given below in it-

`=VLOOKUP(G5,INDIRECT(F5),3,FALSE)`

⏩ Press the **Enter **button.

⏩ Then drag the **Fill Handle** icon to copy the formula and you will see the output like the image below-

⏬** Formula Breakdown:**

**➤**** INDIRECT(F5)**The

**INDIRECT**function will return the reference to a range like-

**{“Hat”,”AB2001″,20;”Jacket”,”AB2002″,50;”Shoe”,”AB2003″,40;”Watch”,”AB2004″,80}**

**➤**** VLOOKUP(G5,INDIRECT(F5),3,FALSE)**Then the

**VLOOKUP**function will give the corresponding result from the range. For

**Cell G5**it returns as-

**80**

**Use Defined Names for Table Array**

Instead of using cell reference, we can use a defined name for a Table Array. That will be quick and time-saving for using the **VLOOKUP** function. Let’s see how to define a name.

**Steps:**

⏩ Select the data range **B5:D11**.

⏩ Then press the **cell name box** and type your chosen name. I have set the name ‘**Table**’.

⏩ Later, just click the **Enter **button.

Now our name is defined successfully.

**Example of Using Defined Name**

Now we can use our defined name for any formula. I’ll show you how to use it in the **VLOOKUP **function.

**Steps:**

⏩ Activate Cell **C13**.

⏩ Write the given formula-

`=VLOOKUP(B13,Table,3,0)`

⏩ Finally, just press the **Enter **button.

**Common Errors in Table Array for What Your VLOOKUP is Not Working**

There are some common problems while using the Table Array in the **VLOOKUP **function for that it will not work and will show** #N/A**.

**Error 1: The Lookup Value is Not in the First Column in the Table Array**

One limitation of **VLOOKUP **is that it can only look for values from the left-most column in the table array. So If your lookup value is not in the first column of the array then you will see the **#N/A** error. Take a look at the image below. Our lookup value is from the second column of the table array that’s why it is showing **#N/A**.

**Solution**

The solution to this problem is not to use **VLOOKUP **at all. Using a combination of the **INDEX** and **MATCH** functions is an effective alternative to VLOOKUP. It is far more versatile.

**Steps:**

⏩ In **Cell C13** type the formula-

`=INDEX(D5:D11,MATCH(B13,C5:C11))`

⏩ Hit the **Enter **button and you will get the right answer.

⏬** Formula Breakdown:**

**➤**** MATCH(B13,C5:C11)**The

**MATCH**function will locate the position of the lookup value from the array

**C5:C11**that will return as-

**1**

**➤**** INDEX(D5:D11,MATCH(B13,C5:C11))**Then the

**INDEX**function will return the value according to the location given from the

**MATCH**function that is-

**20**

**Error 2: The Lookup Value is Smaller Than the Smallest Value in the Table Array**

If the lookup value becomes smaller than the lowest value of the array then the **VLOOKUP **function will return an error too. See the image below that all of our arguments are right but the lookup value is smaller, so it is showing an error.

**Solution**

The first solution is to Correct the lookup value as necessary. And the second solution is to use set the match type **(-1) Greater Than** in the **MATCH **function like the image below. But for that, the array should be in descending order. Let’s fix it.

**Steps:**

⏩ Select the data range **D5:D11**.

⏩ Click as follows: **Home > Editing > Sort & Filter > Sort Largest to Smallest.**

⏩ Then mark **Expand the selection**.

⏩ Finally, press **Sort**.

Now see that it is fixed.

**Error 3: Table Reference Not Locked**

When you are searching for multiple lookup values to return different information about a record and if you don’t lock the table reference then you may get an error while using the Fill Handle tool to copy the formula as shown in the image below.

**Solution**

Just lock the table array reference to get rid of this problem.

**Steps:**

⏩ Press on the reference within the formula.

⏩ Then press the **F4** key to change the reference from relative to an absolute reference.

Now, look that the problem is solved.

**Conclusion**

I hope all of the methods described above will be good enough to use table array in vlookup. Feel free to ask any question in the comment section and please give me feedback.