Forum Discussion

kdwork's avatar
kdwork
Copper Contributor
Nov 03, 2021
Solved

IF/OR INDEX MATCH STATEMENT

Please, Please Help.  I have my last major hurdle to fix on a complicated project I have been trying to get done for way too long between busy times.  I am using Windows 10 Enterprise with Excel 365 ...
excel
Formulas and Functions
  • Detlef_Lewin's avatar
    Detlef_Lewin
    Nov 03, 2021

    kdwork 

     

    =IFERROR(
  INDEX('ITL_Bulk Varieties'!$AK$2:$AK$8,
    MATCH($C$6,'ITL_Bulk Varieties'!$E$2:$E$8,0)),"")
&IFERROR(
  INDEX('DOMESTIC VARIETIES'!$AK$2:$AK$32,
    MATCH($C$6,'DOMESTIC VARIETIES'!$E$2:$E$8,0)),"")

     

Yea_So's avatar
Yea_So
Bronze Contributor
Nov 03, 2021

kdwork 

 

try this:

=IF(OR($E$3={"CAN.INTL","BULK"}),IF(OR(FILTER(INDEX(('ITL_Bulk Varieties'!$AI$2:$AI$8,'ITL_Bulk Varieties'!AJ2:AJ8),,,MATCH('NEW OFFICE COPY'!$S$9,'ITL_Bulk Varieties'!$AI$1:$AJ$1,0)),('ITL_Bulk Varieties'!$A$2:$A$8='NEW OFFICE COPY'!E3)*('ITL_Bulk Varieties'!$E$2:$E$8='NEW OFFICE COPY'!C6))=0,ISERROR(FILTER(INDEX(('ITL_Bulk Varieties'!$AI$2:$AI$8,'ITL_Bulk Varieties'!AJ2:AJ8),,,MATCH('NEW OFFICE COPY'!$S$9,'ITL_Bulk Varieties'!$AI$1:$AJ$1,0)),('ITL_Bulk Varieties'!$A$2:$A$8='NEW OFFICE COPY'!E3)*('ITL_Bulk Varieties'!$E$2:$E$8='NEW OFFICE COPY'!C6)))),100-R10-R11-R12,FILTER(INDEX(('ITL_Bulk Varieties'!$AI$2:$AI$8,'ITL_Bulk Varieties'!AJ2:AJ8),,,MATCH('NEW OFFICE COPY'!$S$9,'ITL_Bulk Varieties'!$AI$1:$AJ$1,0)),('ITL_Bulk Varieties'!$A$2:$A$8='NEW OFFICE COPY'!E3)*('ITL_Bulk Varieties'!$E$2:$E$8='NEW OFFICE COPY'!C6))),IFERROR(OFFSET('DOMESTIC VARIETIES'!$A$1,MATCH(1,INDEX(($E$3='DOMESTIC VARIETIES'!$A$2:$A$600)*($C$6='DOMESTIC VARIETIES'!$E$2:$E$600),,),0),MATCH($S$9,'DOMESTIC VARIETIES'!$A$1:$CP$1,0)-1,1,1),100-R10-R11-R12))

 

You can also change the $S$9 to Minima G

 

cheers

 

Resources