A better way of doing this vlookup?

[rfinnegan]

Hi All:

I'm trying to create a worksheet that will show the sum of 4 other worksheets. Each of the 4 worksheets has 70-80 items on it and no 2 worksheets have all the same items.

I'm using the following vlookup to perform the process. My question -- is there a better way to do this?

=IF(ISNA(VLOOKUP(C2,'Br PLU'!C1:H100,3,0)),0,VLOOKUP(C2,'Br PLU'!C1:H100,3,0))+IF(ISNA(VLOOKUP(C2,'Sto PLU'!C1:H100,3,0)),0,VLOOKUP(C2,'Sto PLU'!C1:H100,3,0))+IF(ISNA(VLOOKUP(C2,'Sixth PLU'!C1:H100,3,0)),0,VLOOKUP(C2,'Sixth PLU'!C1:H100,3,0))+IF(ISNA(VLOOKUP(C2,'Pas PLU'!C1:H100,3,0)),0,VLOOKUP(C2,'Pas PLU'!C1:H100,3,0))

Thanks in advance

 

[CWatts]

That's about as good as I can get. If you have excel 2007 or newer, you can shorten it a little bit with this:

=IFERROR(VLOOKUP(C2,'Br PLU'!C1:H100,3,0),0)+IFERROR(VLOOKUP(C2,'Sto PLU'!C1:H100,3,0),0)+IFERROR(VLOOKUP(C2,'Sixth PLU'!C1:H100,3,0),0)+IFERROR(VLOOKUP(C2,'Pas PLU'!C1:H100,3,0),0)

 

[T. Valko]

Hi All:

I'm trying to create a worksheet that will show the sum of 4 other worksheets. Each of the 4 worksheets has 70-80 items on it and no 2 worksheets have all the same items.

I'm using the following vlookup to perform the process. My question -- is there a better way to do this?

=IF(ISNA(VLOOKUP(C2,'Br PLU'!C1:H100,3,0)),0,VLOOKUP(C2,'Br PLU'!C1:H100,3,0))+IF(ISNA(VLOOKUP(C2,'Sto PLU'!C1:H100,3,0)),0,VLOOKUP(C2,'Sto PLU'!C1:H100,3,0))+IF(ISNA(VLOOKUP(C2,'Sixth PLU'!C1:H100,3,0)),0,VLOOKUP(C2,'Sixth PLU'!C1:H100,3,0))+IF(ISNA(VLOOKUP(C2,'Pas PLU'!C1:H100,3,0)),0,VLOOKUP(C2,'Pas PLU'!C1:H100,3,0))

Thanks in advance

This formula is much shorter but uses the volatile function INDIRECT.

=SUMPRODUCT(SUMIF(INDIRECT("'"&{"Br","Sto","Sixth","Pas"}&" PLU'!C1:C100"),C2,INDIRECT("'"&{"Br","Sto","Sixth","Pas"}&" PLU'!E1:E100")))