In this article, we will learn in detail about table array in Excel including using, and applying variables, with defined names, from another worksheet, using **VLOOKUP**, and **INDIRECT **functions.

We usedÂ **Microsoft**Â **365**Â to prepare this article. You can apply the mentioned functions in versions fromÂ **Excel 2007**Â onwards.

Table array refers to a feature that allows you to organize and manage data filtering, sorting, and automatic formatting in Excel. It involves creating a table that provides a range of cells to store data. As it provides a structured format, it simplifies data analysis, creating charts, and generating reports.

In the following, you will find an overview of table array to lookup values in Excel.

**Download Practice Workbook**

**Table of Contents**Expand

## What Is Table Array in Excel?

A table array refers to a range of cells that contains organized data in rows and columns. It is typically used as an input range in formulas and functions to perform calculations or look up values.

## How to Use Table Array in Excel?

There are 4 ways to use table array in Excel.

- Creating basic table array
- Using defined names table array
- Applying variable table array
- Using table array from another worksheet

### 1. Can We Create Basic Table Array?

You can create a basic table array using ** the VLOOKUP function** in Excel. A basic table array is a simple structured range of data organized into columns and rows. Here the

**VLOOKUP**function will lookup values from the table array.

- Simply, choose a
**cell**(**C17**) and write the below formula down.

`=VLOOKUP(B17,B5:D14,3,FALSE)`

- Here,
**B5:D14**is the table array.

### 2. What Is the Way to Use Defined Names Table Array?

You can also name the table array using defined names table array. A defined label or name represents a specific cell, range of cells, formula, or constant value. By using defined names you donâ€™t need to use cell references for the table array.

- Selecting
**cells**(**B5:D14**) visit the**Name Box**. - Type your desired name for the chosen cells.

- Now, apply the below formula with the defined names to get the proper output.

`=VLOOKUP(B17,Table,3,0)`

### 3. How to Apply Variable Table Array?

You can also use formulas for variable table array in Excel. Excel tables can have variable sizes, that can expand based on the data you input or remove. This dynamic behavior is one of the advantages of using Excel tables.

Suppose we have a dataset of multiple tables and we want to lookup the same item from those tables.

- For this, we will combine the
**VLOOKUP**and**INDIRECT**functions.

`=VLOOKUP(C12,INDIRECT(B12),3,FALSE)`

- We will use the formula below to find the value from the next table-

`=VLOOKUP(C13,INDIRECT(B13),3,FALSE)`

**Formula Breakdown**

**INDIRECT(B13)**

The **INDIRECT** function is used to convert the text in **cell** (**B13**) into a valid reference.

**VLOOKUP(C13,INDIRECT(B13),3,FALSE)**

The **VLOOKUP** function performs a vertical lookup in a table array and returns the corresponding value from the third column.

### 4. Can We Use Table Array from Another Worksheet?

You can use the table array from another worksheet. By using table array from another worksheet you can simplify data management and analyze information effortlessly.

- Choose a
**cell**(**C5**) from the worksheet where you want to see the output. - Next, write the formula and visit the
**Dataset**sheet to choose your table array.

`=VLOOKUP(B5,Dataset!B5:D14`

- Hence, selecting the array complete the formula and hit
**ENTER**to get the result.

`=VLOOKUP(B5,Dataset!B5:D14,3,FALSE)`

## How to Use VLOOKUP with Multiple Table Arrays in Excel?

You can combine the **VLOOKUP **and **IFNA** functions to use **VLOOKUP** with multiple table arrays in Excel. A multiple table array refers to having more than one structured table of data in an Excel workbook. In Excel, you can perform lookups across multiple table arrays by using a combination of **VLOOKUP** and **IFNA** functions.

- Choose a
**cell**(**C12**) and write the below formula. - Now, hit
**ENTER**.

`=IFNA(VLOOKUP(B12,B5:D9,3,FALSE),(VLOOKUP(B12,F5:H9,3,FALSE)))`

**Formula breakdown**

**VLOOKUP(B12, B5:D9, 3, FALSE**

Here, the **VLOOKUP** function searches for the value in **cell** (**B12**) within the range **B5:D9** looking for an exact match specified by **FALSE**.

**VLOOKUP(B12,F5:H9,3,FALSE**

Similarly, It searches for the value in **cell** (**B12**) within the range **F5:H9** and also looks for an exact match.

**IFNA(VLOOKUP(B12,B5:D9,3,FALSE),(VLOOKUP(B12,F5:H9,3,FALSE)))**

Finally, the **IFNA** function stands as ignoring the error and moving to the next part of the formula.

**Read More:** What Is Table Array in Excel VLOOKUP?

## How to Solve VLOOKUP with Table Array Is Not Working in Excel?

There are 3 ways to solve VLOOKUP with table array not working in Excel.

- Making lookup column must be the first column
- Creating lookup value must be equal to or greater than smallest value
- Locking table reference while auto filling

### 1. How to Make Lookup Column Must be the First Column?

You need to make the lookup column ad the first column to solve **VLOOKUP** with table array is not working in Excel. If your lookup value is not in the first column then you will face a **#N/A error**Â just like the following image.

`=INDEX(D5:D14,MATCH(B17,C5:C14))`

**Read More:** How to Use VLOOKUP Table Array Based on Cell Value in Excel

### 2. Can We Create Lookup Value Must Be Equal or Greater Than Smallest Value?

You can create lookup value must be equal or greater than smallest value to solve **VLOOKUP** with table array is not working in Excel. If the lookup value is smaller than the table array, it will return a **#N/A** **error**.

Suppose we have a dataset where we have multiple products with **Product Code**Â and **Price**.

- Now if we lookup a value using the
**VLOOKUP**function. - We will find a
**#N/A error**as the lookup value is smaller than the values in the table array.

- To solve this, if we use a lookup value equal to or greater than the table array value then we will get the exact output we are looking for approximate match.

`=VLOOKUP(B17,C5:D14,2,TRUE)`

### 3. How to Lock Table Reference While Auto Filling?

You can lock table reference while auto filling to solve this table array not working issue. Using absolute reference inside the formula will lock the table references. Using absolute references in this way allows you to maintain a consistent table reference while auto-filling.

While applying **Fill Handle** you will find an error if your table reference value is not locked.

- Using the absolute reference, lock values from the table array.
- Thus you will get the output ignoring the
**#N/A error**.

`=VLOOKUP(B17,$B$5:$D$14,3,FALSE)`

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

## Which Things Should You Remember?

- While using the table array, review and validate the data to identify and rectify any inconsistencies or errors.
- You must ensure your table array has clear and unique headers for each column. Headers are essential for identifying and referencing data within the table.
- You need to maintain the structured format of the table with headers in the first row and data below. Donâ€™t insert empty rows or columns within the table, as this can disrupt its functionality.

## Frequently Asked Questions

**1. How can I filter data within a table array?**

**Answer:** In order to filter data you can use the filter dropdowns that automatically appear in the header row of each column in the table array.

**2. Can I convert a table array back into a normal range of cells?**

**Answer: **Yes, you can convert a table array back into a normal range of cells by selecting the table array, visiting the**Â Table Design** tab, and clicking on the **Convert to Range**Â button.

**3. Can I add formulas to calculated columns in a table array?**

**Answer: **Of course, you can add formulas to calculated columns in a table array.

## Conclusion

In conclusion, table array in Excel provides a powerful way to organize and manage data in a structured manner. With proper use of table arrays, you can efficiently work with large datasets, perform calculations, and simplify formula creation. Using **INDEX-MATCH** functions provides flexible lookup in table array instead of the **VLOOKUP** function.Â Please inform us in the comment section about your experience. We, the **Exceldemy**Â team, are always responsive to your queries. Stay tuned and keep learning.

## Table Array in Excel: Knowledge Hub

**How to Name a Table Array in Excel****How to Edit Table Array in Excel****How to Expand Table Array in ExcelÂ****How to Find Table Array in Excel**

**<< Go Back to Excel VLOOKUP Function | Excel Functions | Learn Excel**