multiple vlookups - what else?

[lennyphil]

Dear experts,

I'd like to apply a multiple vlookup in my datasheet - data with the same description shall be found and summed up over several data sheets.
The formula (tbfound at the end of the thread) I used (a simple if then + vlookup one) is too long for Excel. Can you provide a shorter one?
Thanks a lot in advance for any help you can provide!!

Lennard

P.S. DE VISA or DE Master are names of the data sheets used.

=if(isna(VLOOKUP(B38;'DE VISA'!B38:I51;3;FALSE));0;VLOOKUP(B38;'DE VISA'!B38:I51;3;FALSE))
+if(isna(VLOOKUP(B38;'DE Master'!B38:I51;3;FALSE));0;VLOOKUP(B38;'DE Master'!B38:I51;3;FALSE))
+if(isna(VLOOKUP(B38;'DE Amex'!B38:I51;3;FALSE));0;VLOOKUP(B38;'DE Amex'!B38:I51;3;FALSE))
+if(isna)VLOOKUP(B38;'NL VISA'!B38:I51;3;FALSE));0;VLOOKUP(B38;'NL VISA'!B38:I51;3;FALSE))
+if(isna)VLOOKUP(B38;'NL MASTER'!B38:I51;3;FALSE));0;VLOOKUP(B38;'NL MASTER'!B38:I51;3;FALSE))
+if(isna)VLOOKUP(B38;'NL AMEX'!B38:I51;3;FALSE));0;VLOOKUP(B38;'NL AMEX'!B38:I51;3;FALSE))
+if(isna)VLOOKUP(B38;'FR VISA'!B38:I51;3;FALSE));0;VLOOKUP(B38;'FR VISA'!B38:I51;3;FALSE))
+if(isna)VLOOKUP(B38;'FR MASTER'!B38:I51;3;FALSE));0;VLOOKUP(B38;'FR MASTER'!B38:I51;3;FALSE))
+if(isna)VLOOKUP(B38;'FR Amex'!B38:I51;3;FALSE));0;VLOOKUP(B38;'FR Amex'!B38:I51;3;FALSE))
+if(isna)VLOOKUP(B38;'FR Carte Bleue'!B38:I51;3;FALSE));0;VLOOKUP(B38;'FR Carte Bleue'!B38:I51;3;FALSE))
+if(isna)VLOOKUP(B38;'UK VISA'!B38:I51;3;FALSE));0;VLOOKUP(B38;'UK VISA'!B38:I51;3;FALSE))
+if(isna)VLOOKUP(B38;'UK Master'!B38:I51;3;FALSE));0;VLOOKUP(B38;'UK Master'!B38:I51;3;FALSE))
+if(isna)VLOOKUP(B38;'UK Amex'!B38:I51;3;FALSE));0;VLOOKUP(B38;'UK Amex'!B38:I51;3;FALSE))
+if(isna)VLOOKUP(B38;'UK VISA Debit'!B38:I51;3;FALSE));0;VLOOKUP(B38;'UK VISA Debit'!B38:I51;3;FALSE))
+if(isna)VLOOKUP(B38;'UK Maestro'!B38:I51;3;FALSE));0;VLOOKUP(B38;'UK Maestro'!B38:I51;3;FALSE))
+if(isna)VLOOKUP(B38;'UK Solo'!B38:I51;3;FALSE));0;VLOOKUP(B38;'UK Solo'!B38:I51;3;FALSE))

[DonkeyOte]

The only alternative without installing a 3rd party add-in is really:

=SUMPRODUCT(SUMIF(INDIRECT("'"&Z1:Z15&"'!B38:B51"),B38,INDIRECT("'"&Z1:Z15&"'!C38:C51")))

where Z1:Z15 contains your sheet names, DE VISA, DE Master etc... through to UK Solo

NOTE: the use of INDIRECT makes this Volatile - which in turn makes it bad news in big models.

You might also want to investigate morefunc.xll and the THREED function included within, see: http://www.mcgimpsey.com/excel/threedsumif.html

[Mallycat]

Usually if you have a problem like this, it suggests that there may be a better approach. It looks like you are trying to aggregate different credit card statements for reporting purposes.

Is it possible that you could put all the data on one sheet, and add an additional column that represents the source of the data (ie DE Amex, DE VISA)?

If you do this, you could run a pivot table over the top of the single sheet and it will give you all the reporting you could dream of 'out of the box'.

Matt