IFNA formula not working

[rosrow31]

Hi,

I have the formula: =IF(ISBLANK(X6),"",IFNA(IF(X6="Yes",VLOOKUP(A6,'Storage Pivt'!A:B,2,FALSE),0),0)). The cell is returning 0 when it hsouldn't be, it sould be returning the results of the V lookup.

The formula is looking every thing up correctly. It's the IFNA part of the formula that isn't working correctly, as the cell is displaying displaying 0. However, the cell in X6 does = Yes so it should be using the vlookup, instead it is using the false part and displaying 0. I know this because if you replace the last zero with another number, it displays the new number. E.g. if the formula was =IF(ISBLANK(X6),"",IFNA(IF(X6="Yes",VLOOKUP(A6,'Storage Pivt'!A:B,2,FALSE),0),99)) it would return 99

 

[mikerickson]

Change the formula to this to see which of the 0's is being returned. It may be that X6 has a trailing space

=IF(ISBLANK(X6),"",IFNA(IF(X6="Yes",VLOOKUP(A6,'Storage Pivt'!A:B,2,FALSE),999),0))

 

[Fluff]

instead it is using the false part and displaying 0. I know this because if you replace the last zero with another number, it displays the new number. E.g. if the formula was =IF(ISBLANK(X6),"",IFNA(IF(X6="Yes",VLOOKUP(A6,'Storage Pivt'!A:B,2,FALSE),0),99)) it would return 99

Actually the 99 is the return for IFNA, not for the false part of the IF. Therefore the value in A6 was not found n the other sheet. Check that those values are either text or numbers, but not a mix of both.