Discussion Locked

Eliminate N/A's in Excel Cells

0 helpful
4 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Often I run AS400 queries, then import the data into pivot tables that are
accessed by various worksheets.

I use the vlookup and hlookup functions to utilize that data. When the
vlookup cannot find a particular value it returns an N/A to therequesting
cell.

How can I do away with these annoying N/A's so the people using these
workbooks just see a blank or text string in those cells instead?

I have tried if statements with no luck.

Thanks,

Rick
rvs1@sceinet.com

4 replies

by al hedstrom

Try this: Use the IF function to test if a cell meets an error condition using ISERROR. If it does, instruct it to substitute a text string like "No Data." It should look something like
=IF(ISERROR(B10,"No Data"))

Experiment with the possibilities. Good luck.

Al Hedstrom

by gurnerworld

I think that you will need to combine the If with the vlookup.

It should be structured as follows:

=if(iserror(vlookup(value,table,column,false),"",(vlookup(value,table,column,false)

The above formula basically says, if doing that vlookup produces any error (not just N/A), then leave the cell blank. Otherwise, just do the vlookup.

If you want it to only do this with N/A and not all error messages, replace ISERROR with ISNA.

by rvs

Mike,
Thanks, Your answer put me on the right track. By adding additional braces it will get the job done. Here's the formula:
=IF(ISERROR(VLOOKUP(B6*100,Pivot!$A$5:$N$154,D$3,FALSE)),"none",(VLOOKUP(B6*100,Pivot!$A$5:$N$154,D$3,FALSE)))

This discussion is closed to new replies.