Pivot Table returning Zero instead of Blanks

[hcabs99]

Hi

I have a pivot table which is taking data from a field which is populated as follows

=IF(VLOOKUP(B90,'Order Download'!A:BP,62,0)="","",VLOOKUP(B90,'Order Download'!A:BP,62,0))

If this lookup returns a value, all good and i get that into the pivot. If it returns blank (which is the same the data it is looking up) , then this appears on the pivot as 0 . I want it to return blank

I've tried a number of varieties on the check box in the Pivot table options for the "For Empty cells show..", but no matter what i do , i get zero.

Any ideas please?

 

[william_man]

If you don't mind having some errors showing in your data field, you can replace the blanks with errors and then check the 'For Error Values Show' option:

=IF(VLOOKUP(B90,'Order Download'!A:BP,62,0)="",[B]NA()[/B],VLOOKUP(B90,'Order Download'!A:BP,62,0))