Match Function with multiple criteria

[Greygoose]

Hello,

I have two sheets in one file. The first is used for a pivot table and the second one is constantly updated with NOTES on each order.

I tried to link the NOTES for each product by an Index function but have not been able to get a result. The idea is to use multiple criteria to assure that the NOTES posted for each specific item in a specific order are correct to later be presented in a pivot table.

The criteria I need to use are in columns A, B, C, D, E of sheet1.
The same criteria are located in the same order in sheet2 as: F, G, H, I, J. The NOTES I need to find for each product are in column M in sheet2. There are 100 lines in sheet2.

The formula I wrote in row 2, sheet1 is:

=INDEX('sheet2'!M$2:M$100,MATCH(A2&B2&C2&D2&E2,'sheet2'!$F$2:$F$100&'sheet2'!$G$2:$G$100&'sheet2'!$H$2:$H$100&'sheet2'!$I$2:$I$100&'sheet2'!$J$2:$J$100))

Please help!

Thank you in advance for your feedback

 

[Andrew Poulsom]

Welcome to MrExcel.

Try:

=INDEX(Sheet2!M$2:M$100,MATCH(1,INDEX((Sheet2!F$2:F$100=A2)*(Sheet2!G$2:G$100=B2)*(Sheet2!H$2:H$100=C2)*(Sheet2!I$2:I$100=D2)*(Sheet2!J$2:J$100=E2),),FALSE))

 

[Greygoose]

Worked perfectly!

Thank you for the prompt feedback.

Best regards