What Is Table Array in Excel VLOOKUP?

Get FREE Advanced Excel Exercises with Solutions!

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.

Regular Table Array in the Same Excel Worksheet

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.

Regular Table Array from Another Excel Worksheet

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

Regular Table Array from Another Excel Worksheet

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

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

Regular Table Array from Another Excel Worksheet

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.

Variable Table Array

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

Defined Names for Table Array

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.

Example of Using Defined Name

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.

The Lookup Value is Not in the First Column in the Table Array

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.

The Lookup Value is Not in the First Column in the Table Array

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.

The Lookup Value is Smaller Than the Smallest Value in the Table Array

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.

The Lookup Value is Smaller Than the Smallest Value in the Table Array

Steps:

⏩ Select the data range D5:D11.

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

The Lookup Value is Smaller Than the Smallest Value in the Table Array

⏩ Then mark Expand the selection.

⏩ Finally, press Sort.

The Lookup Value is Smaller Than the Smallest Value in the Table Array

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.

Table Reference Not Locked

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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo