VLOOKUP AND ROW PROBLEM

[mrbeanyuk]

I have three master/summary spreadsheets which are linked in several ways to about 40 others. I have three because it is too much info to put in one. One linking is via VLOOKUP.

The problem I have is that a partilcar row has the VLOOKUP data in but if the result is zero it returns a #N/A across the whole row with the exception of one or two cells which may have data. I would like to add to my forumla that if the row has a figure in any cell of that row, all #N/As go blank in that row.

Is this achievable?

Thanks and help!

 

[beccy]

have you tried incorporating the isna function to eliminate any N/As?

 

[mrbeanyuk]

isna function? Will this get rid of all the n/a's as I only want this to happen on several rows and leave N/A's in some

Thanks

 

[bsnapool]

you could also try the iserror function, - if there is any error this will bring back a blank or something you choose....

|Example)

=IF(ISERROR(VLOOKUP(E3,$B$3:$C$11,2,FALSE)),"",VLOOKUP(E3,$B$3:$C$11,2,FALSE))

 

[mrbeanyuk]

WORKS A TREAT! Thank you!

 

[pgc01]

Hi all

Using ISERROR in this case should be avoided. It can lead to overlooking other type of errors. Use ISNA instead.

Using bsnapool's example

=IF(ISNA(VLOOKUP(E3,$B$3:$C$11,2,FALSE)),"",VLOOKUP(E3,$B$3:$C$11,2,FALSE))

ISNA with VLOOKUP tells you if the value was not found. In that case you write the empty string.

Now suppose this formula is in A10 and you copy it up until A1. You will notice that in A1:A7 you have the error #REF! which tells you that there is something wrong in that formula, in this case you are off the worksheet. You can then review your logic and correct the formula.

If you had used ISERROR you would not be warned about the error (that has nothing to do with VLOOKUP, it's an invalid address error).

Unsing ISERROR instead of ISNA may lead to having invalid formulas in the worksheet without knowing.

Hope this helps
PGC