vlookup across multiple files returning #N/A

[Reebis]

I have 4 spreadsheets, each listing different accounts for the 4 qtr of the
year. I'm trying to roll them up into one spreadsheet, and I figure vlookup
is the easiest way after i do subtotals for the different accounts in each
qtr. So i basically have this format:

=vlookup(1st Qtr)+vlookup(2nd Qtr)....(4th Qtr)

Syntax is returning correct values except when there is no value in one fo
the qtrs, it makes the whole thing kick out the dreaded #N/A.

Any thought on how to get aorund this? Would I have to enclose each vlookup
in an IF(ISNA) loop?

Thanks in advance.

-bob

 

[Teethless mama]

=IF(ISNA(your lookup formula1),0,your lookup formula1)+IF(ISNA(your lookup
formula2),0,your lookup formula2) and so on...

 

[bpeltzer]

Yes, you'd need to check for NA for each vlookup. The reason is that NA
added to a number is NA, so you've got to get rid of them all if you want to
add up the results.

 

[Martin Fishlock]

Just as a design issue, you may find it easier to have four columns in your
summary sheet and then add up those. It will show where errors are and then
you can hide the columns if you don't want to see them after.

Otherwise if yiu just do a sum over the cells then you really don't know if
there are problems with the data.

Another way to do it and remove a lot of external vlookups is to copy and
paste the qtr sheets into youe summary book and then just do sums if the
format is the same.