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.

**What Is 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 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.

**Using the Table Array Argument in Excel VLOOKUP: ****3 Useful Examples**

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.

**Read More: **How to Edit Table Array in Excel

**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 is located 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.

**Read More:** How to Find Table Array in Excel

**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**

### Example 4: 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.

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.

**Read More: **How to Name a Table Array in Excel

**Why Is Excel VLOOKUP Not Working and How to Resolve**

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**.

**Reason 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 by the

**MATCH**function that is-

**20**

**Reason 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.

**Reason 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.

**Read More:** How to Lock Table Array in Excel

**Download Practice Book**

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

**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.

## Related Articles

**<< Go Back to Table Array in Excel | Excel VLOOKUP Function | Excel Functions | Learn Excel**