How to set format and match 2 criteria data files

[Yvonneyeong]

How to set format and match 2 criteria data files

 

[AliGW]

Change the formula to this:

=MAX(0,SUM(IFERROR(INDEX(K3:N9,MATCH(D6,J3:J9,0),MATCH(B6,K2:N2,0)),"~")-SUM(IFERROR(INDEX(O3:R9,MATCH(D6,J3:J9,0),MATCH(B6,K2:N2,0)),"~"))))

 

[bosco_yip]

Perhaps,

In F6 enter :

=MAX(0,(VLOOKUP(D6,J2:R9,MATCH(B6,J2:N2,0),0)-VLOOKUP(D6,J2:R9,MATCH(B6,J2:N2,0)+4,0)))

or,

=MAX(0,SUM(INDEX((K3:N9,O3:R9),MATCH(D6,J3:J9,0),MATCH(B6,K2:N2,0),N(IF(1,{1,2})))*{1,-1}))

Regards
Bosco

 

[Yvonneyeong]

Change the formula to this:

=MAX(0,SUM(IFERROR(INDEX(K3:N9,MATCH(D6,J3:J9,0),MATCH(B6,K2:N2,0)),"~")-SUM(IFERROR(INDEX(O3:R9,MATCH(D6,J3:J9,0),MATCH(B6,K2:N2,0)),"~"))))

Thanks a lot for your help! That's easily way for understanding at learning curve

 

[Yvonneyeong]

Perhaps,

In F6 enter :

=MAX(0,(VLOOKUP(D6,J2:R9,MATCH(B6,J2:N2,0),0)-VLOOKUP(D6,J2:R9,MATCH(B6,J2:N2,0)+4,0)))

or,

=MAX(0,SUM(INDEX((K3:N9,O3:R9),MATCH(D6,J3:J9,0),MATCH(B6,K2:N2,0),N(IF(1,{1,2})))*{1,-1}))

Regards
Bosco

Thanks a lot for your help! However, can please teach me what's the meaning of the formula written with "N(IF(1,{1,2})))*{1,-1}))"