Index/match multiple criteria, multiple results

Brandypants

New Member
Joined
Nov 7, 2019
Messages
3
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!
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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.
 
Upvote 0
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!
 
Upvote 0
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
 
Last edited:
Upvote 0
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!!
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top