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.
- Creating basic table array
- Using defined names table array
- Applying variable table array
- 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.
- 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
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.
=INDEX(D5:D14,MATCH(B17,C5:C14))
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.
=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
- 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
Get FREE Advanced Excel Exercises with Solutions!