Solved VLOOKUP not bring back the correct figures.

October 9, 2013 at 06:36:01
Specs: Windows 7
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?

See More: VLOOKUP not bring back the correct figures.

October 9, 2013 at 07:22:02
✔ Best Answer
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.


Report •

October 9, 2013 at 07:47:06
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'.

Report •

October 9, 2013 at 08:24:32
Have you tried stepping through the formula?

On the Ribbon select the Evaluate Formula and/or the Error Checking buttons and see if that gives a hint as to what's happening to generate the error.


Report •

Related Solutions

October 9, 2013 at 09:35:19
Just as another idea, you can check if your Numbers are really numbers
with the =ISNUMBER() function

Open up a new column next to your lookup values and add the formula:


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.


Report •

Ask Question