VLOOKUP in Excel Table Not Working? Fix It

When VLOOKUP in an Excel table is not working, first check that the value you are searching for is in the table’s leftmost column, use the correct return-column number, and request an exact match. Then check for misspelled values, extra spaces, or numbers stored as text; these are common reasons for a result such as #N/A or an unexpected match.

Start with the three essential checks

VLOOKUP searches from left to right. It looks for a value in the first column of its lookup range, then returns a value from another column in the same row. The return-column number is counted from the left edge of that range, not from the worksheet’s column letters. For text, codes, and most ordinary lookups, use an exact match rather than relying on the function’s default approximate-match behavior.

For example, assume an Excel table named Products has the columns SKU, Product, and Price, in that order, and the SKU to search for is in E2. This formula returns the Price from the third column when the SKU matches exactly:

With the lookup value in E2 and the Products table columns ordered SKU, Product, Price, enter =VLOOKUP(E2,Products,3,FALSE). If the matching item’s price is 19.95, the result is 19.95.

Check these details before changing the formula:

Lookup column: SKU must be the first column of Products. VLOOKUP cannot search a later column and return a value from a column to its left.
Return-column number: 3 means the third column of the lookup range. If you accidentally use 2, the result is Product, not Price.
Match setting: FALSE asks for an exact match. Leaving out the fourth argument can produce incorrect results because VLOOKUP then uses approximate matching, which has sorting requirements.
Lookup value: The value in E2 must match the table entry in content and data type, not merely look similar on screen.

How VLOOKUP behaves with an Excel table

An Excel table is a named, expanding range with column headers, filters, and other table features. When you use the table name as the lookup range, VLOOKUP can refer to the whole table without a fixed address such as A2:C500. If you add rows to the table, the named table range expands, so the formula can include the new records automatically.

To check or change a table’s name in Excel for Microsoft 365 on Windows, click inside the table and look for the Table Design tab. The Table Name box is on that tab. Names cannot contain spaces, so Excel may turn a name such as Product List into Product_List. Use the name displayed in Excel exactly in the formula. If Excel says the name is invalid, confirm that the range is actually a table and that you have not used a worksheet name or a column heading in its place.

Tables also support structured references, which use table and column names instead of cell coordinates. These can make a formula easier to read, especially when it is entered in another table. For example, suppose an Orders table has a SKU column and a Price column, while Products has SKU, Product, and Price. In the Price column of Orders, a row-specific reference can use the SKU on that same row:

With the formula in the Price column of Orders, and Products ordered SKU, Product, Price, enter =VLOOKUP([@SKU],Products,3,FALSE). Each row looks up its own SKU and returns the matching Products price.

The notation [@SKU] means the SKU in the current Orders row. If the formula is outside a table, use a cell reference such as E2 instead. A frequent mistake is copying a formula that uses a relative cell reference down a worksheet and finding that the lookup value has shifted to the wrong column or row. Structured references can reduce that risk in a calculated table column.

Diagnose common error messages and wrong results

The error or result often points toward the problem. Start by inspecting one failing lookup value and the corresponding table entry. Compare the underlying values, not just their displayed formatting.

#N/A: No exact match was found. Check spelling, leading or trailing spaces, hidden characters, and whether one value is text while the other is numeric. Also confirm the lookup value is in the table’s first column.
#REF!: The return-column number is larger than the number of columns in the lookup range, or a referenced range or table column was deleted. Count the available columns from the lookup range’s left edge and correct the index.
#VALUE!: The column index is not a usable positive number, or an argument is otherwise invalid. Check that the index is a whole number from 1 through the number of columns in the range.
#NAME?: Excel may not recognize the table name or a name used in the formula. Check the spelling in Table Design and make sure the table name is not enclosed in quotation marks.
A plausible but wrong result: The formula may be using approximate match, the wrong column index, or the first of several duplicate lookup values. Confirm the match argument and inspect the column order.

A lookup range that includes the header row is not automatically an error, but it can make the setup harder to understand and may lead you to miscount the return column. Using the table name keeps the range tied to the table itself. Also check whether a cell shows a formula as text instead of calculating it: the cell may be formatted as Text, or the entry may begin with an apostrophe. Change the format to General, then re-enter the formula so Excel evaluates it.

Check numbers, text, spaces, and duplicate values

Two values can look identical and still fail an exact lookup. For instance, a SKU stored as the number 1042 is not the same data type as the text value 1042. This can happen when data is imported from a CSV, copied from a website, or assembled from different systems. Number formatting alone does not convert text into a number; a text value can be displayed with the same digits as a numeric value.

Use Excel’s cell alignment and warning indicators as clues, but do not treat them as proof. Select the lookup cell and the matching table cell, then inspect their values and number formats. Convert the values consistently: decide whether the field is meant to be numeric or textual, and make both sides that type. Product codes with leading zeroes, for example, are often identifiers rather than quantities; converting them to numbers can remove those zeroes.

Spaces and invisible characters are another frequent cause of #N/A. A value copied from an external source may have a leading space, a trailing space, or a nonbreaking space that ordinary visual inspection misses. Clean the source data when possible, especially if many rows are affected. Excel’s TRIM function can remove ordinary extra spaces in a cleanup column, but it does not remove every possible hidden character, so compare the cleaned result with the original before replacing data.

Exact-match VLOOKUP returns the first matching record it encounters. If the lookup column contains duplicates, a successful result may still be the wrong one for your purpose. Sort order does not make duplicate records unique. Check whether each key is supposed to identify one record; if not, decide which record should be returned or use a method designed to return multiple matches.

Make the formula robust inside a table

When a formula is entered in a table column, Excel often fills it down as a calculated column. That is useful, but inspect the first few rows to confirm the formula uses the intended lookup value on every row. If one cell in the column has been edited separately, it may no longer follow the same formula as the rest.

Be cautious when changing the table’s column order. VLOOKUP’s numeric return index is tied to position, not to a header name. If you insert a column between SKU and Price, a formula that formerly returned the third column may now return a different field. After changing table structure, verify the output column rather than assuming the formula still means Price.

Keep the lookup key and the return value in the same lookup table, with the key at the left edge. If the data is arranged with the key to the right of the return value, VLOOKUP cannot return to the left. You can rearrange the source, create a separate lookup range with the needed order, or choose another lookup method. Avoid hiding a structural problem by changing match settings; a formula that returns a value is not necessarily returning the right value.

In some regional settings, Excel expects semicolons rather than commas between function arguments. The formulas here use commas, as shown in English-language Excel with those settings. If Excel rejects a formula immediately with a syntax warning, your list separator may differ; use the separator configured for your system. This is separate from an error such as #N/A, which means the formula was accepted but the requested match was not found.

When to use XLOOKUP or INDEX and MATCH

VLOOKUP remains useful for a simple left-to-right lookup, and it is available in many older Excel versions. If its leftmost-column limitation or numeric return index is causing repeated problems, another lookup function may fit the data better. XLOOKUP is available in Microsoft 365 and Excel 2021 or later; it can search one column and return a value from a separate column, including one to the left.

Assume E2 contains a SKU, and Products has separate SKU and Price columns. This XLOOKUP searches Products[SKU] and returns the corresponding value from Products[Price]:

With the lookup value in E2 and Products containing SKU and Price columns, enter =XLOOKUP(E2,Products[SKU],Products[Price]). If the SKU is not found, the formula returns #N/A by default.

INDEX and MATCH are another option, including in older Excel versions. MATCH finds the position of a key in one column, and INDEX returns the value at that position in another. This avoids VLOOKUP’s requirement that the key column be first, although the formula is longer and still needs clean, consistent lookup values.

With the lookup value in E2 and Products containing SKU and Price columns, enter =INDEX(Products[Price],MATCH(E2,Products[SKU],0)). The zero in MATCH requests an exact match. Choose a replacement because it suits the workbook’s layout or Excel version, not because a different function will automatically fix inconsistent source data.

Frequently Asked Questions

Why does VLOOKUP return #N/A in an Excel table?

Usually the exact lookup value is not present in the table’s first column, or the two values differ because of spaces, hidden characters, spelling, or a text-versus-number mismatch. Check the value itself and confirm that the formula uses an exact match.

Does VLOOKUP work with Excel table names?

Yes. You can use a table name such as Products as the lookup range. The table must contain the lookup column and the return column, with the lookup column at its left edge. Check the table name on Table Design if Excel does not recognize it.

Why is VLOOKUP returning the wrong column?

The return-column number counts from the first column of the lookup range, not from worksheet column A. Count across the table or range again, then confirm that the number still points to the desired field after any columns were inserted or moved.

Does VLOOKUP need the table to be sorted?

Not when the formula requests an exact match with FALSE. Approximate matching has different requirements and can return a nearby value rather than an exact one. For codes, names, and most table lookups, explicitly request an exact match.

Can VLOOKUP return a value from a column to the left?

No. VLOOKUP searches the first column in its range and returns data from a column to its right. Rearrange the lookup data or use XLOOKUP or INDEX and MATCH when the return column is to the left.

Why did a formula work before I added a table column?

VLOOKUP uses a numeric return-column position. Inserting a column can change which field that position refers to, even if the formula still calculates. Recheck the returned values after changing a table’s columns, or use a lookup method that specifies the return column separately.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.