I would look into using the VLOOKUP function to search on that many
conditions. Look in Excel's help for its syntax.
--
Brevity is the soul of wit.


"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