How to Find Table Array in Excel (4 Suitable Examples)

Need to learn how to find the table array in Excel? If you are looking for such unique kinds of tricks, you’ve come to the right place. Here, we will take you through four easy and convenient examples of finding the table array in Excel.

What Is Table Array?

Table Array is one of the most important arguments of any the LOOKUP function such as the the VLOOKUP function or the the HLOOKUP function. The VLOOKUP function searches for a particular value within a range (named table_array by MS Excel) to return a value from a specified column in the same row. We use the table array to find specific data or formulas or groups of formulas or data. If you use this function, it will search the particular data you have inserted. So, giving the name of the table array can be helpful for simplifying the work.

How to Find Table Array in Excel: 4 Useful Examples

Let’s get introduced to our dataset first. We have a report of Daily Sales- Fruit Section of a certain grocery store. Here, columns B, C, and D contain the Product names, names of Customers, and Quantity respectively.

Now, we’ll select cells in the B4:D14 range. Then name the range as MyTable on the box at the top-left of the column heading.

Now, we’ll find this table array in various ways on our different examples.

Example 01: Using Name Manager to Find Table Array in Excel

In our first example, we’ll use Name Manager to find the table array in Excel. Also, we can apply a keyboard shortcut to open the Name Manager. To do this using the first method, you may follow the below steps.

📌 Steps:

At the very beginning, go to the Formulas tab.
Secondly, click on the Defined Names group drop-down.
Then, select Name Manager from the available options.

Instantly, the Name Manager dialog box opens up.
Here, we can see our named range in the list. Simply, select MyTable.
Afterward, click on the upside arrow beside the box of Refers To:

Immediately, it opens the Name Manager – Refers To: input box.
We can see the cell range of MyTable in the box. Also, cells in the B4:D14 gets bounded by a green dashed line to indicate the named range in the worksheet.

Note: Name Manager in the input box refers to the name of the current worksheet. See the picture above for a better understanding.

Example 02: Utilizing Go To Feature to Find Table Array in Excel

For those of you who want to learn about more techniques, there is another way to find the table array in Excel. We could possibly use the Go To feature under the Find & Select option in Excel. It’s simple & easy, just follow along.

📌 Steps:

First of all, press CTRL + G on your keyboard.

Right away, it opens the Go To dialog box.
Later, select MyTable under the section of Go To:.
Lastly, click OK.

In a flash, it highlights the cell range of the named range MyTable, which is B4:D14. It also creates a green solid line around the range.
That’s how we can find our table array in Excel.

Example 03: Find Table Array in VLOOKUP in Excel

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. To find the table array in VLOOKUP, follow the steps carefully.

📌 Steps:

Initially, in cell C17, we determined the number of sales of Kiwis using VLOOKUP.

After that, select cell C17 and we can see the formula below.

=VLOOKUP(B17,VLOOKUP!MyTable,3)

Here, B17 refers to Kiwi. Then, VLOOKUP!MyTable indicates the table_array argument. And 3 means the col_index-num.

Here, we can see the table_array as a name. But, if we want to know the cell range of the named range, what should we do? To know this, be with us.

When we put the cursor on VLOOKUP!MyTable in the Formula Bar, it highlights the table_array argument. So, we can be sure this is the table array in our formula.

Besides, it resembles the same color in VLOOKUP!MyTable and cells in the B4:D14 range. Both feature red color. From that, we can be sure that they are the same entity.

Finally, select the table_array in the Formula Bar and press F9 on your keyboard. You can see the argument in array form.

Example 04: Find Table Array in HLOOKUP in Excel

Similarly, we can find the table array in HLOOKUP in Excel. Allow me to demonstrate the process step-by-step.

📌 Steps:

Firstly, select cell C17.
Then, we can find the formula in the Formula Bar like below.

=HLOOKUP(C16,HLOOKUP!MyTable,6)

Here, C16 resembles Product which is the lookup_value. Besides, HLOOKUP!MyTable serves as table_array argument. Also, 6 represents the row_index_num.

When we put the cursor on HLOOKUP!MyTable in the Formula Bar, it highlights the table_array argument. So, we can be sure this is the table array in our formula.
Besides, it resembles the same color in HLOOKUP!MyTable and cells in the B4:D14 range. Both feature red color. From that, we can be sure that they are the same entity.

At last, select the table_array in the Formula Bar and press F9 on your keyboard. You can see the argument in array form.

How to Find Lookup Array in XLOOKUP in Excel

The XLOOKUP function allows you to look for a value in a dataset and return the corresponding value in some other row/column. You can see the syntax of this function here. To find the lookup_array in XLOOKUP, follow the steps carefully.

📌 Steps:

At first, select cell C17.
Then, we can find the formula in the Formula Bar like below.

=XLOOKUP(B17,Products,D4:D14)

Here, B17 represents Lemon which is the lookup_value. Then, Products and D4:D14 serve as the lookup_array and return_array correspondingly.

When we put the cursor on Products in the Formula Bar, it highlights the lookup_array argument. So, we can be sure this is the lookup array in our formula.
Besides, it resembles the same color in Products and cells in the B4:B14 range. Both feature red color. From that, we can be sure that they are the same entity.

Lastly, select the Products in the Formula Bar and press F9 on your keyboard. You can see the argument in array form.

That’s how we can find the lookup array in XLOOKUP in Excel.

Practice Section

For doing practice by yourself we have provided a Practice section like below in each sheet on the right side. Please do it by yourself.

Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.

Finding Table Array.xlsx

Conclusion

This article provides easy and brief solutions to finding the table array in Excel. Don’t forget to download the Practice file. Thank you for reading this article, we hope this was helpful. Please let us know in the comment section if you have any queries or suggestions.

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

Get FREE Advanced Excel Exercises with Solutions!