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

The table below is 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

Method 1 – Creating basic table array

• Select a cell (C17) and enter the following formula below.

• B5:D14 is the table array.

Method 2 – Use Defined Names Table Array

• Select cells (B5:D14) and go to the Name Box.
• Enter a name.

• Enter the following formula below, with the defined name.

Method 3 – Apply Variable Table Array

The sample dataset below contains multiple tables and we want to lookup the same item from those tables.

• Combine the VLOOKUP and INDIRECT functions.

• Enter the formula below to find the value from the next table,

Method 4 – Table Array from Another Worksheet

• Select a cell (C5).
• Enter the formula and go to the Dataset sheet to choose your table array.

• Press ENTER to get the result.

How to Use VLOOKUP with Multiple Table Arrays in Excel?

• Select a cell (C12) and enter the formula below.
• 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

How to Make Lookup Column Must be the First Column?

If the lookup value is not in the first column you will face a #N/A error as show in the image below.

• To solve this error, combine the INDEX and MATCH functions.

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

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.

The sample dataset below have multiple products with Product Code and Price.

• If we lookup a value using the VLOOKUP
• The #N/A error message will appear, as the lookup value is smaller than the values in the table array.

• To solve this, use a lookup value equal to or greater than the table array value.

How to Lock Table Reference While Auto Filling?

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

• Using the absolute reference, lock values from the table array.
• Get the output ignoring the #N/A error.

Read More: How to Lock Table Array in Excel

Things To 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.

Table Array in Excel: Knowledge Hub

• How to Name a 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