Index/match multiple criteria, multiple results

[Brandypants]

I need to review a list of data and return all of the matches based on 2 criteria and cannot figure out the entire formula. Below is what I have now but it only matches 1 criteria: "CASH IN IMPORT'!$R$5. How on earth do I add a second criteria, which would be column B of sheet '3.INT' matching cell H4 of sheet "CASH IN IMPORT'


=IFERROR(INDEX('3.INT'!$A$1:$F$60,SMALL(IF('3.INT'!$A:$A='CASH IN IMPORT'!$R$5,ROW('3.INT'!$A:$A)),ROW(1:1)),3),"")

Any thoughts are appreciated!

 

[RasGhul]

Hi,

Maybe try,

=IF(ROWS($A$2:A2)>COUNTIFS('3.INT'!$A$2:$A$60,$R$5,'3.INT'!$B$2:$B$60,$H$4),"",INDEX('3.INT'!A$1:A$60,SMALL(IF('3.INT'!$A$1:$A$60&'3.INT'!$B$1:$B$60='CASH IN IMPORT'!$R$5&'CASH IN IMPORT'!$H$4,ROW('3.INT'!$A$1:$A$60)-ROW('3.INT'!$A$1)+1),ROWS(A$2:A2))))

Enter with CTRL+SHIFT+ENTER as normal into one cell then drag across to column F first and then drag down as many columns as you may need.

 

[Brandypants]

I have absolutely no idea what you are doing but IT WORKS!!!! except that it is not picking up the last row of the data being indexed; I tried to change a few of the row references but nothing worked. Ideas?

I can't thank you enough; I have been trying to get this to work for 3 days!

 

[RasGhul]

Hi Brandy,

Change this bit

COUNTIFS('3.INT'!$A$1:$A$60,$R$5,'3.INT'!$B$1:$B$60,$H$4

I use the rows & countifs because it runs better than iferror

 

[Brandypants]

Hi Brandy,

Change this bit

COUNTIFS('3.INT'!$A$1:$A$60,$R$5,'3.INT'!$B$1:$B$60,$H$4

I use the rows & countifs because it runs better than iferror

ok, so once again I have no clue why that worked but I am greatly appreciative!!! That tweak was the final step on a 12 month project and it feels great to be done!!

 

[RasGhul]

Sounds good, glad we could help.