Formula Help - Better than Using the IF(ISERR Method

[comish4lif]

Here's my formula:

=IF(SEARCH("TYPE
TRANSACTION:",INDIRECT($A5&"!a9"),1)>1,"A9",IF(SEARCH("TYPE
TRANSACTION:",INDIRECT($A5&"!a10"),1)>1,"A10",IF(SEARCH("TYPE
TRANSACTION:",INDIRECT($A5&"!a11"),1)>1,"A11",IF(SEARCH("TYPE
TRANSACTION:",INDIRECT($A5&"!a12"),1)>1,"A12",IF(SEARCH("TYPE
TRANSACTION:",INDIRECT($A5&"!a13"),1)>1,"A13",IF(SEARCH("TYPE
TRANSACTION:",INDIRECT($A5&"!a14"),1)>1,"A14","MORE"))))))

Which returns a #VALUE error if the text string "TYPE TRANSACTION" is
not found in the first search location (a9) - in this case. The else
condition should command the nxt IF statement. But since it
encountereed an error, and not a number >1, it stops evaluating and
displays the #Value error.

Now, I can do this over with:

=if(iserr(SEARCH("TYPE TRANSACTION:",INDIRECT($A5&"!a9"),1), [do the
next search, and so on],[then all the ELSE statements)

Does this make sense? Can anyone suggest a more elegant way to do this?

I am using indirect because cell A2 containts a workbook name and once
I get this formula working in this cell, I will fill down to generate
answers for a number of sheets.

 

[Domenic]

Try...

=INDEX({"A9","A10","A11","A12","A13","A14"},MATCH(TRUE,ISNUMBER(SEARCH("T
ype Transaction:",INDIRECT($A5&"!A9:A14"))),0))

or

=INDEX($D$1:$D$6,MATCH(TRUE,ISNUMBER(SEARCH("Type
Transaction:",INDIRECT($A5&"!A9:A14"))),0))

....where D16 contains A9, A10, A11...A14. Note that both these
formulas need to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!