Table Array in Excel (Create, Use and Fix Issues)

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

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.

1. Creating basic table array
2. Using defined names table array
3. Applying variable table array
4. 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.

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

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.

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

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.

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

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.

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.

1. Making lookup column must be the first column
2. Creating lookup value must be equal to or greater than smallest value
3. 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.

• To solve this error simply use the below formula.
• Simply, combine the INDEX and MATCH functions.

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.

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.

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