Index and match with multiple criteria

Apple08

Active Member
Joined
Nov 1, 2014
Messages
353
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.
 

Some videos you may like

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
Joined
Jun 12, 2014
Messages
46,303
Office Version
  1. 365
Platform
  1. Windows
Check that the cell is not formatted as text, also check on the formula tab that "Show formulas" is not highlighted.
 

Apple08

Active Member
Joined
Nov 1, 2014
Messages
353
Hi Fluff, I have checked that the cell was formatted as general, and 'Show formulas' isn't highlighted.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,303
Office Version
  1. 365
Platform
  1. Windows
When you look in the formula are the {} visible?
 

Apple08

Active Member
Joined
Nov 1, 2014
Messages
353

ADVERTISEMENT

Yes they are visible
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,303
Office Version
  1. 365
Platform
  1. Windows
In that case I'm not sure
 

Apple08

Active Member
Joined
Nov 1, 2014
Messages
353

ADVERTISEMENT

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
Joined
Jun 12, 2014
Messages
46,303
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 1, 2014
Messages
353
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
Joined
Jun 12, 2014
Messages
46,303
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,985
Messages
5,526,063
Members
409,685
Latest member
Davetom

This Week's Hot Topics

Top