Match two criteria and return multiple records

[fish0321]

I am having a problem with returning data that matches two criteria in different columns. I have been playing around with the COUNTIFS,INDEX, and MATCH functions but I cant seem to get it right. I have enclosed an example of what I am looking for.

[bebo021999]

Base-To

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($B$3:$B$15)/(($B$3:$B$15="a1")*($D$3:$D$15="a1")+($B$3:$B$15="a2")*($D$3:$D$15="a2")),ROW(1:1))),"")

Base-from (Change D to E)

2 ways to display time : format as time or using MOD function (cell with yellow).

[fish0321]

Bebo21999,


Thanks for the help, but I am still running into a problem where not all the data that matches is getting copied over. please see attached file.

[bebo021999]

Your current formula:
=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($B$6:$B$500)/(($B$6:$B$500="ORD")*($H$6:$H$500="ORD")),ROW(14:14))),"")
starts counting from 14th occurence of "ORD"
try to replace it with ROW(1:1) from first row.

[fish0321]

Bebo2199,

Thank You, ROW fix and everything seems to to working great. I do have one other issue i am working on. I am trying to count time for specific ranges. the current formula i am using is: =COUNTIFS(range,">="&start time cell,range,"<"&end-time cell), it seems not to calculate the results. once again, i have included an example and thank you in advance for helping me out, much appreciated.

[bebo021999]

AL4:

Please Login or Register  to view this content.

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).