Condense a formula

  • Thread starter Thread starter gotroots
  • Start date Start date
G

gotroots

Hi

I want to include several more sheets to the following formula.

=IF(ISNA(MATCH(A2,'Uses A'!$B:$B,0)),IF(ISNA(MATCH(A2,'RHS
additions'!B:B,0)),"No Match Found",A2),A2)

The formula will be a heck of a size if up to 10 more sheets are added.
These sheets start with the name 'Uses' ie. 'Uses B', 'Uses C' etc. and will
contain the same range as (A2,'Uses A'!$B:$B,0) for example (A2,'Uses
B'!$B:$B,0)

Is there a condensed formula suitable for this task.
 
put all the sheet names in the cell:

ex:
H1: Uses A
H2: Uses B
H3: Uses C
and so on...

"MySheets" is a define name range H1:H3

=IF(SUMPRODUCT(COUNTIF(INDIRECT("'"&MySheets&"'!B:B"),A2)),A2,"No Match
Found")
 
Back
Top