You might consider using a range of helper cells (12 or whatever) containing
your individual MATCH/VLOOKUP formulas and then just one SUM formula to add
them together.

hth
Vaya con Dios,
Chuck, CABGx3



hth
Vaya con Dios,
Chuck, CABGx3



"Andy777" wrote:

I'm having trouble finding a way to condense my formula which searches up to
40 sheets for an entry in the first column of that sheet (if it exists),
which matches a value in column A on Sheet1, and then returns the value in
column 6 of that sheet.
An extract of my formula (which works fine) is below:

=IF(ISERROR(MATCH($A1,Sheet2!$A:$A,0)),0,VLOOKUP($ A1,Sheet2!$A:$IV,6,FALSE))+IF(ISERROR(MATCH($A1,Sh eet3!$A:$A,0)),0,VLOOKUP($A1,Sheet3!$A:$IV,6,FALSE ))+
....

My problem is that after about twelve ...+IF(... functions I run out of
space in the formula bar to keep entering them. Does anyone know of a way to
condense or contract these IF functions so my formula is much shorter? Any
help would be greatly appreciated.

Thanks,
Andrew