I have a VLOOKUP formula looking at a simple pivot table =IF(ISNA(VLOOKUP(D10,'Pivot Table'!$A$2:$B$2000,2,FALSE)),"0",(VLOOKUP(D10,'Pivot Table'!$A$2:$B$2000,2,FALSE))). D10 is a value it is looking for in column A of the 'Pivot Table'! worksheet, and the value from column B is returned. For some lookups the value returned is correct, and on others they return #N/A even though there's not information missing?

What are the Values that you are using in Cells D10 and A2:B2000? Are they comparable? Are you sure the Value your looking for is within the look-up range?

Make sure the values in your Lookup are actually the values you think they are.

If you have imported the data, then Dates may look like dates, but are actually Text, the same applies to Numbers.

MIKE

As all the values are numbers I've set the formats on all cells to General as that is the format on the pivot table cells which the formula is looking at. I have checked a number of the lookups and there is a value to bring back. D10 for example brings back '57' which is correct but D22 brings back #N/A when the value on the table is '32'.

Have you tried stepping through the formula? On the Ribbon select the

Evaluate Formulaand/or theError Checkingbuttons and see if that gives a hint as to what's happening to generate the error.MIKE

Just as another idea, you can check if your Numbers are really numbers

with the =ISNUMBER() functionOpen up a new column next to your lookup values and add the formula:

=ISNUMBER(A1)

If it returns TRUE then it's a number, otherswise it's Text

Also, in regards to the #N/A error message:

Excel VLOOKUP Error Messages

The following error messages are associated with VLOOKUP.

AN #N/A error is displayed if:

The lookup value is not found in the first column of the table array.

The range for the table array argument is inaccurate.

For example, the argument may include empty columns on the left side of the table array.The range lookup argument is set to true and an exact match for the lookup_value argument cannot be found in the first column of the table array.

MIKE

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History