Adding VLOOKUPS across multiple worksheets

  • Thread starter Thread starter NCarter
  • Start date Start date
N

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?
 
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!
 
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.
 
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.
 
Back
Top