An Excel table array lookup usually means using the table_array argument in VLOOKUP: it tells Excel which range contains both the value to find and the result to return. For example, if a table named ProductList has columns SKU, Product, and UnitPrice, and the SKU to find is in A2, use =VLOOKUP(A2,ProductList[[SKU]:[UnitPrice]],3,FALSE) to return the matching price.
What “table array” means in Excel
“Table array” is the name of VLOOKUP’s second argument, not a special formula type or a requirement to create an official Excel Table. It is the rectangular block of cells that VLOOKUP searches. The first column of that block must contain the lookup values; other columns can hold the information you want returned.
In the example above, ProductList[[SKU]:[UnitPrice]] identifies the table’s three adjacent columns. The 3 tells VLOOKUP to return a value from the third column of that selected block, and FALSE asks for an exact match. VLOOKUP counts columns from the left edge of the array, not from the worksheet’s column A and not from the table’s original position on the sheet.
The phrase can also cause confusion because an Excel Table, with a name and filter buttons, is different from a VLOOKUP table_array. You can use a normal cell range as the array, or use a structured reference to an Excel Table. The key is that the selected array must include the lookup column and the return column.
How VLOOKUP uses the array
VLOOKUP evaluates its arguments in order: the value to find, the table array, the return-column number, and the match mode. It looks for the lookup value in the array’s leftmost column. Once it finds a matching row, it returns the value from the numbered column in that same row.
Suppose a worksheet has SKU values in B2:B100, product names in C2:C100, and prices in D2:D100. If the requested SKU is in A2, the following formula searches the three-column block and returns its third column, the price.
With the lookup SKU in A2 and the lookup table in B2:D100, use =VLOOKUP(A2,B2:D100,3,FALSE). If A2 contains SKU-104 and that row’s price in column D is 12.5, the result is 12.5.
That column number is relative to B2:D100: B is column 1, C is column 2, and D is column 3. If you instead select C2:D100, the price becomes column 2 of that array, but VLOOKUP can no longer search the SKU in column B because it is outside the selected range. Choosing the correct left edge is therefore as important as choosing the right return-column number.
Build a lookup with an Excel Table
Excel Tables can make a lookup easier to read and maintain as data grows. To create one in Microsoft Excel for Microsoft 365 on Windows, select a cell in the data and choose Insert > Table, confirm the range and whether it has headers, then choose OK. On the Table Design tab, set a meaningful name in the Table Name box, such as ProductList. Table names cannot contain spaces.
Assume ProductList has the headers SKU, Product, and UnitPrice, in that order, and the lookup SKU is in A2. The structured reference below selects those three table columns as the array:
With the lookup SKU in A2 and the table columns SKU through UnitPrice in ProductList, use =VLOOKUP(A2,ProductList[[SKU]:[UnitPrice]],3,FALSE). For a matching row whose UnitPrice is 12.5, this returns 12.5.
The reference expands when rows are added to the table, so you do not have to revise a fixed endpoint such as row 100. It also makes the formula’s scope clearer to someone reading the workbook. If the lookup formula is entered in a calculated column inside the same table, a row reference such as [@SKU] can refer to the current row’s SKU; outside the table, use a regular cell reference such as A2 or a suitable structured reference.
On Excel for Mac, Tables and structured references are also supported, though ribbon labels and their placement can vary by release. Excel for the web supports tables too; some editing controls may be arranged differently from the Windows desktop ribbon. The formula’s basic VLOOKUP behavior is the same.
Choose exact or approximate matching
The final VLOOKUP argument controls whether Excel seeks an exact value or an approximate one. For codes, names, product IDs, and most ordinary lookup tasks, use FALSE (or 0) to request an exact match. If omitted, the argument defaults to approximate matching, a frequent source of surprising results.
With a numeric lookup value in A2 and an ascending, sorted band table in B2:C6, where B contains lower bounds and C contains rates, =VLOOKUP(A2,B2:C6,2,TRUE) returns the result for the largest lower-bound value that is less than or equal to A2. Approximate matching is useful for deliberately defined bands, such as tier thresholds, but requires the first column to be sorted in ascending order for dependable results. An unsorted list of product codes is not a suitable approximate-match array.
Exact matching is not the same as “similar” matching. For text, Excel generally compares the lookup value with the cell contents as an exact value, although VLOOKUP’s exact mode is not case-sensitive. A trailing space or a number stored as text rather than as a number can still prevent an expected match. Clean or standardize the source values when records that look alike fail to match.
Important limits and design choices
- The lookup column must be first. VLOOKUP searches only the leftmost column of its table_array. It cannot search a column and return a result from a column to its left.
- The return-column number is positional. It counts from the left edge of the selected array. Inserting or moving columns inside a fixed range can make a hard-coded number return a different field than intended.
- The array should include the needed return field. If the return column is outside the selected rectangle, VLOOKUP cannot return it.
- Duplicates return the first matching row. If the lookup column contains repeated keys, VLOOKUP does not automatically return all matching records or flag that the key is duplicated.
- Exact matching is usually the safer default. Specify FALSE explicitly so the formula’s intended behavior is visible and does not depend on the default.
These limits matter when designing a workbook, not just when correcting a formula. Put a stable unique key near the left side of the lookup data, use descriptive headers, and decide whether duplicate keys are valid. If a key is expected to be unique, checking for duplicates in the source may be more useful than changing the lookup formula.
Common errors and how to fix them
#N/A means VLOOKUP did not find the requested value in the array’s first column for an exact lookup. Check for extra spaces, inconsistent data types, a typo, or a lookup range that omits the relevant row. Also confirm that the key column is actually the array’s leftmost column.
#REF! can occur when the column index asks for a column beyond the width of the selected array. Count the array’s columns from left to right and adjust the index. If a formula returns a plausible but incorrect field, the index may still be within range but point to the wrong column.
To display a clearer message for a missing key, wrap the lookup in IFNA. With the lookup value in A2 and the same ProductList table as above, use =IFNA(VLOOKUP(A2,ProductList[[SKU]:[UnitPrice]],3,FALSE),"SKU not found"). A found price of 12.5 remains 12.5; an unmatched SKU displays SKU not found. IFNA only handles the #N/A result, so it does not hide other errors such as an invalid column index.
A final diagnostic is to test the lookup value and source key directly: compare their displayed contents, inspect whether one is text and the other numeric, and check for invisible spaces. For repeated formulas over a conventional range, consider using absolute references such as $B$2:$D$100 so the range does not shift when the formula is filled down. Structured table references generally avoid the need to maintain row endpoints.
VLOOKUP compared with XLOOKUP and INDEX/MATCH
VLOOKUP remains useful for compatibility and straightforward left-to-right lookups. Its limits are real, though: it relies on a column number, and its search column must be the first column of the array. If the source layout changes, a formula using a hard-coded index can become harder to maintain.
In Microsoft 365, XLOOKUP can specify the lookup and return columns separately, so the return column can be to the left or right of the lookup column. It also uses exact match by default. With the lookup value in A2, ProductList[SKU] as the lookup column, and ProductList[UnitPrice] as the return column, use =XLOOKUP(A2,ProductList[SKU],ProductList[UnitPrice],"SKU not found"). For the same matching price of 12.5, it returns 12.5; if no key matches, it returns the supplied message. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but not in some older Excel releases.
INDEX and MATCH can also separate the lookup column from the return column and work in older Excel versions. They take more explaining than VLOOKUP, and the best choice depends on workbook compatibility and the layout. If a file will be opened in older software, check which functions are supported by everyone who needs to use it. Do not replace a functioning VLOOKUP just for style if its limitations are not causing a problem.
Frequently Asked Questions
What do I put in table_array in VLOOKUP?
Put the rectangular range that contains the lookup column and the column you want returned. The lookup column must be the first column in that range. You can use a cell range such as B2:D100 or an Excel Table structured reference.
Does table_array have to be an Excel Table?
No. In VLOOKUP, table_array is simply the argument name for the searched range. An official Excel Table is optional, but its structured references can expand as the table grows and make formulas easier to understand.
Why does VLOOKUP return the wrong column?
The column index is counted from the first column of the selected array, not from the worksheet. Recount the selected columns from left to right and confirm the index points to the desired return field.
Why does VLOOKUP show #N/A when the value is present?
Check whether the value is in the array’s first column and whether the formula uses the right range. Extra spaces, inconsistent text-versus-number formats, or a small difference in the key can stop an exact match.
Can VLOOKUP return a value from a column to the left?
No. Its lookup column must be the leftmost column of its array, and its return column must be to the right. Use XLOOKUP in a supported Excel version, or use an INDEX/MATCH approach when the return field is on the left.
Should I use FALSE or TRUE?
Use FALSE for an exact match, which is appropriate for most identifiers and text keys. Use TRUE only when approximate matching is intended and the first column is sorted in ascending order, such as a table of threshold bands.
Related
- table() in RStudio: How It Counts and Displays Data
- Data Validation and Reconciliation in Excel Explained
- PowerPoint Table Diagonal Split Cell: Practical Methods
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel COUNT Formula Examples: Numbers, Blanks, and TextIn Excel for Microsoft 365, use =COUNT(A2:A20) to count cells containing numbers, including dates and times. For other…
- How to Multiply Hours in Excel: Formulas and FormattingTo multiply a duration by a factor in Excel, enter the duration in A2 and the factor in…
- Excel Formula for Hours Worked: Calculate Time and PayFor a shift that starts in A2 and ends in B2, enter =MOD(B2-A2,1)*24 to return hours as a…
- Excel SEQUENCE Examples for Numbers, Dates, and MoreExcel’s SEQUENCE function creates a spill range of consecutive numbers or other values: enter =SEQUENCE(5) in a blank…
- Excel Date Format Formulas: TEXT, DATEVALUE, and MoreTo display a date as text with an Excel formula, use TEXT; with a date in A2, =TEXT(A2,"mmmm…
- Excel Text Filter for Multiple Words: How It WorksTo filter Excel text for multiple words, open the column’s filter menu and choose Text Filters > Custom…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.