MATCH limitations?

A

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
 
B

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)
 
P

PapaDos

=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,"-")))))
 
D

daddylonglegs

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)
 
A

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
 
Top