MATCH limitations?

[AA Arens]

How many MATCH commands can be added in one formula?

I got an error while I double check this long formula, particulary the
( and ).

"The formula.... contains an error..."

If I remove a section of MATCH, the MATCH error incicator moves to one
MATCH to the back.

This is the formula:

=IF(ISNUMBER(MATCH(C7,'Batavia Air'!I$7:I$206,0)),'Batavia
Air'!$C$1,IF(ISNUMBER(MATCH(C7,'Batavia Air'!Z$35:AC$35,0)),'Batavia
Air'!$C$1,IF(ISNUMBER(MATCH(C7,'Multi Structure'!I$7:I$206,0)),'Multi
Structure'!$C$1,IF(ISNUMBER(MATCH(C7,'Multi
Structure'!Z$35:AC$35,0)),'Multi
Structure'!$C$1,IF(ISNUMBER(MATCH(C7,'PEC Tech'!I$7:I$206,0)),'PEC
Tech'!$C$1,IF(ISNUMBER(MATCH(C7,'PEC Tech'!Z$35:AC$35,0)),'PEC
Tech'!$C$1,IF(ISNUMBER(MATCH(C7,JCB!I$7:I$206,0)),JCB!$C$1,IF(ISNUMBER(MATCH(C7,JCB!Z$35:AC$35,0)),JCB!$C$1,IF(ISNUMBER(MATCH(C7,Adhimix!I$7:I$206,0)),Adhimix!$C$1,IF(ISNUMBER(MATCH(C7,Adhimix!Z$35:AC$35,0)),Adhimix!$C$1,"")))))



Bart

 

[Bob Phillips]

It is not MATCH, it is too many nested fuctions in one formula.

You need to restructure your data, break the formula down, or use another
approach. For instance

=IF(ISNUMBER(MATCH(C7,'Batavia Air'!I$7:I$206,0)),'Batavia Air'!$C$1,"")&
IF(ISNUMBER(MATCH(C7,'Batavia Air'!Z$35:AC$35,0)),'Batavia Air'!$C$1,"")&
IF(ISNUMBER(MATCH(C7,'Multi Structure'!I$7:I$206,0)),'Multi
Structure'!$C$1,"")&
IF(ISNUMBER(MATCH(C7,'Multi Structure'!Z$35:AC$35,0)),'Multi
Structure'!$C$1,"")&
IF(ISNUMBER(MATCH(C7,'PEC Tech'!I$7:I$206,0)),'PEC Tech'!$C$1,"")&
IF(ISNUMBER(MATCH(C7,'PEC Tech'!Z$35:AC$35,0)),'PEC Tech'!$C$1,"")&
IF(ISNUMBER(MATCH(C7,JCB!I$7:I$206,0)),JCB!$C$1,"")&
IF(ISNUMBER(MATCH(C7,JCB!Z$35:AC$35,0)),JCB!$C$1,"")&
IF(ISNUMBER(MATCH(C7,Adhimix!I$7:I$206,0)),Adhimix!$C$1,"")&
IF(ISNUMBER(MATCH(C7,Adhimix!Z$35:AC$35,0)),Adhimix!$C$1,"")

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Guest]

=IF(OR(ISNUMBER(MATCH(C7,'Batavia
Air'!I$7:I$206,0)),ISNUMBER(MATCH(C7,'Batavia Air'!Z$35:AC$35,0))),'Batavia
Air'!$C$1,IF(OR(ISNUMBER(MATCH(C7,'Multi
Structure'!I$7:I$206,0)),ISNUMBER(MATCH(C7,'Multi
Structure'!Z$35:AC$35,0))),'Multi
Structure'!$C$1,IF(OR(ISNUMBER(MATCH(C7,'PEC
Tech'!I$7:I$206,0)),ISNUMBER(MATCH(C7,'PEC Tech'!Z$35:AC$35,0))),'PEC
Tech'!$C$1,IF(OR(ISNUMBER(MATCH(C7,JCB!I$7:I$206,0)),ISNUMBER(MATCH(C7,JCB!Z$35:AC$35,0))),JCB!$C$1,IF(OR(ISNUMBER(MATCH(C7,Adhimix!I$7:I$206,0)),ISNUMBER(MATCH(C7,Adhimix!Z$35:AC$35,0))),Adhimix!$C$1,"-")))))

 

[Guest]

Try

=CHOOSE(MATCH(9.9999999999999E+307,CHOOSE({1,2,3,4,5,6,7,8,9,10,11},0,MATCH(C7,Adhimix!Z$35:AC$35,0),MATCH(C7,Adhimix!I$7:I$206,0),MATCH(C7,JCB!Z$35:AC$35,0),MATCH(C7,JCB!I$7:I$206,0),MATCH(C7,'PEC
Tech'!Z$35:AC$35,0),MATCH(C7,'PEC Tech'!I$7:I$206,0),MATCH(C7,'Multi
Structure'!Z$35:AC$35,0),MATCH(C7,'Multi
Structure'!I$7:I$206,0),MATCH(C7,'Batavia
Air'!Z$35:AC$35,0),MATCH(C7,'Batavia
Air'!I$7:I$206,0))),"",Adhimix!$C$1,Adhimix!$C$1,JCB!$C$1,JCB!$C$1,'PEC
Tech'!$C$1,'PEC Tech'!$C$1,'Multi Structure'!$C$1,'Multi
Structure'!$C$1,'Batavia Air'!$C$1,'Batavia Air'!$C$1)

 

[AA Arens]

I nice solution. It works. Three questions:

How many company names can I add, to keep it working? (Adhimix,
Adhimix, Samsung, Samsung.......)
The CHOOSE{1,..,11} will be {1,..,13/15 etc}??
May I choose the MATCH-es in a another order? (I the order I have
sheets as well)

Bart