Adding VLOOKUPS across multiple worksheets

[NCarter]

Hi,

I am trying to add several VLOOKUP results obtained from data in several
worksheets. My formula is : -

=VLOOKUP(A5,'Common Costs - Budget'!A5:X74,2,FALSE)+VLOOKUP(A5,'Brighton -
Budget'!A5:X53,2,FALSE)+VLOOKUP(A5,'Hove - Budget'!A5:X56,2,FALSE)

I have several worksheets all with similar budget information on and, for
example, I need to all the payroll costs from each of the sheets. Not all
the sheets will always have costs on so I was thinking of doing lookups and
adding them together but I just keep getting the #N/A result. Is there a way
to do this?

 

[Ms-Exl-Learner]

Try this…

=IF(A5="","",IF(ISNA(VLOOKUP(A5,'Common Costs -
Budget'!$A$5:$X$74,2,FALSE)),"",IF(VLOOKUP(A5,'Common Costs -
Budget'!$A$5:$X$74,2,FALSE)=0,"","["&VLOOKUP(A5,'Common Costs -
Budget'!$A$5:$X$74,2,FALSE)&"] "))&IF(ISNA(VLOOKUP(A5,'Brighton -
Budget'!$A$5:$X$53,2,FALSE)),"",IF(VLOOKUP(A5,'Brighton -
Budget'!$A$5:$X$53,2,FALSE)=0,"","["&VLOOKUP(A5,'Brighton -
Budget'!$A$5:$X$53,2,FALSE)&"] "))&IF(ISNA(VLOOKUP(A5,'Hove -
Budget'!$A$5:$X$56,2,FALSE)),"",IF(VLOOKUP(A5,'Hove -
Budget'!$A$5:$X$56,2,FALSE)=0,"","["&VLOOKUP(A5,'Hove -
Budget'!$A$5:$X$56,2,FALSE)&"]")))

Remember to Click Yes, if this post helps!

 

[Roger Govier]

Hi

Are all of the costs on the same row of each sheet?
If so, then insert 2 new sheets called First and Last.
Drag them so that they "sandwich" each of your sheets that you wish to
add, and then use
=SUM(FIrst:Last!B5) or whatever is the cell reference for Payroll

Ensure that your Summary sheet is outside of the sandwich, either before
First or after Last.

 

[DKS]

Use the ISNA function to "trap" cases where you risk to get a N/A value. Use
that in conjunction with an IF statement.

So, e.g.

IF (ISNA(<your VLOOKUP formula>), <put a blank or zero depending on your
need>, <your VLOOKUP formula>)

Keep in mind that the resulting formula will be extremely lengthy to read -
but otherwise it should do the trick.

Hope this helps.