# Index and match with multiple criteria

#### Apple08

##### Active Member
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.

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### Fluff

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

#### Apple08

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

#### Fluff

##### MrExcel MVP, Moderator
When you look in the formula are the {} visible?

#### Apple08

##### Active Member

Yes they are visible

#### Fluff

##### MrExcel MVP, Moderator
In that case I'm not sure

#### Apple08

##### Active Member

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

##### MrExcel MVP, Moderator
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

##### Active Member
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

##### MrExcel MVP, Moderator
It needs to be something like
Code:
``````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.

Replies
3
Views
65
Replies
20
Views
159
Replies
7
Views
77
Replies
11
Views
157
Replies
3
Views
75