# What Is a Table Array in VLOOKUP? (Explained with Examples)

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.

## 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. #### Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts 