Index and match with multiple criteria

[Apple08]

Hi All

I have a workbook with two worksheets, Sheet1 is 'Report' and Sheet 2 is 'All', I need to lookup the dates in column J of 'All' based on multiple criteria in 'Report' column D, E and cell K4.

I have done the formula below in Report cell K5 and I need to paste the formula from K5 to the last row of K:

{=index('All'!$J:$J,match(1,($D5='All'!$D:$D)*($E5='All'!$E:$E)*($K$4='All'!$I:$I),0))}

I have use ctrl+shift+enter to input the above formula, but the formula was shown in cell rather than the date result. Please can anyone tell me what have I done wrong?

And if how to put the formula in VBA? Many thanks.

 

[Apple08]

Thank you Fluff! This is very helpful! Cheers!

 

[Fluff]

You're welcome & thanks for the feedback