What Is Table Array in Excel VLOOKUP?

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:

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-

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

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

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

⏩ 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

• How to Use VLOOKUP Table Array Based on Cell Value in Excel
• How to Expand Table Array in Excel

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