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.

 

[Fluff]

Check that the cell is not formatted as text, also check on the formula tab that "Show formulas" is not highlighted.

 

[Apple08]

Hi Fluff, I have checked that the cell was formatted as general, and 'Show formulas' isn't highlighted.

 

[Fluff]

When you look in the formula are the {} visible?

 

[Apple08]

Yes they are visible

 

[Fluff]

In that case I'm not sure

 

[Apple08]

I found out my mistake, I typed in the {} manually with the formula before enter it! I have now got the formula correct. Please could I ask if I need to add it to VBA, do I just put it as normal formula or else? Can it copy down to the last row? Thanks.

 

[Fluff]

If you enter the formula, then confirm with Ctrl Shift Enter, you an then drag the formula down as far as needed.
If either of the neighbouring columns are filled to the last row, then rather than dragging the formula down, you can double click the bottom right hand corner of the cell & it will fill down automatically.

 

[Apple08]

Thanks Fluff, I can manage to copy it down the last row, however, I would like to know how to put it in VBA? Is it the same as normal Range("K5").formula =

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

 

[Fluff]

It needs to be something like

Range("K5").FormulaArray = "=index('All'!$J:$J,match(1,($D5='All'!$D:$D)*($E5='All'!$E:$E)*($K$4='All'!$I:$I),0))"
Range("K5:K" & LastRow).Filldown

But I would recommend limiting the your ranges. Using whole column references in an array formula is very slow.