# Match Index Criteria

• 01-07-2019, 05:09 AM
Match Index Criteria
Need your kind advice, request you to look at the sample 2 sheet that is attached.
Sheet 3 is my resultant sheet need to lookup value from sheet 1 & sheet 2.Attachment 604919
• 01-07-2019, 06:47 AM
Bo_Ry
Re: Match Index Criteria
Please try at Sheet3 E2 and copy down

Formula:
=IFERROR(INDEX(Sheet1!\$C\$1:\$M\$20,MATCH(D2&C2,INDEX(Sheet1!\$A\$1:\$A\$20&Sheet1!\$B\$1:\$B\$20,),),MATCH(B2,Sheet1!\$C\$2:\$M\$2,)),INDEX(Sheet2!\$C\$1:\$M\$20,MATCH(D2&C2,INDEX(Sheet2!\$A\$1:\$A\$20&Sheet2!\$B\$1:\$B\$20,),),MATCH(B2,Sheet2!\$C\$2:\$M\$2,)))
• 01-08-2019, 05:41 AM
Re: Match Index Criteria
Works,but this formula is too heavy, original worksheet is taking hours to calculate.
Any other way this can be can will be appreciated.
Thanx
• 01-08-2019, 08:32 AM
Bo_Ry
Re: Match Index Criteria
Not sure if this faster.
E3

=IFERROR(INDEX(Sheet1!\$C\$1:\$M\$20,MATCH(1,INDEX(1/(Sheet1!\$A\$1:\$A\$20=D3)/(Sheet1!\$B\$1:\$B\$20=C3),)),MATCH(B3,Sheet1!\$C\$2:\$M\$2,)),INDEX(Sheet2!\$C\$1:\$M\$20,MATCH(D3,Sheet2!\$A\$1:\$A\$20,),MATCH(B3,Sheet2!\$C\$2:\$M\$2,)))
• 01-09-2019, 05:11 AM