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

The table below is an overview of table array to lookup values in Excel.

## 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.
`=VLOOKUP(B17,B5:D14,3,FALSE)`
• 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.
`=VLOOKUP(B17,Table,3,0)`

### 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.
`=VLOOKUP(C12,INDIRECT(B12),3,FALSE)`

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

### 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.
`=VLOOKUP(B5,Dataset!B5:D14`

• Press ENTER to get the result.
`=VLOOKUP(B5,Dataset!B5:D14,3,FALSE)`

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

• Select a cell (C12) and enter the formula below.
• Hit ENTER.
`=IFNA(VLOOKUP(B12,B5:D9,3,FALSE),(VLOOKUP(B12,F5:H9,3,FALSE)))`

Formula breakdown

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

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

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)))

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.

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 and MATCH functions.
`=INDEX(D5:D14,MATCH(B17,C5:C14))`

### 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.
`=VLOOKUP(B17,C5:D14,2,TRUE)`

### 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.
`=VLOOKUP(B17,\$B\$5:\$D\$14,3,FALSE)`

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

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

Get FREE Advanced Excel Exercises with Solutions!
Wasim Akram

Wasim Akram holds a BSc degree in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Over the past 2 years, he has been actively contributing to the ExcelDemy project, where he has authored more than 150 articles. Now, he is working as an Excel VBA and Content Developer. He likes learning new things about Microsoft Office, especially Excel VBA, Power Query, Data Analysis, and Excel Statistics. He is also very interested in machine learning and... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF