List values of 2 columns of a row based on criteria

Hello,

I am looking to have a spreadsheet that lists "tickets" that were created and solved in a given date range. In the included example file there is data that is input (in grey) and the expected result is listed below. I am looking to list in one section all ticket numbers and their associated "subject" that were created in the given date range criteria, and all tickets in a separate section that were resolved in the given date range.

In the example I included what the expected result would be, however, I am unsure how to code this.

Help is appreciated. Thank you in advance.List ticket numbers - example.xlsx

I want to do it without filtering as I'll be linking this to a "report" page and will not be able to filter the list.

Originally Posted by oeldere

with index/match.

see the attached file.

The only issue I have with that solution is if the date created is for example 9/5/2014 then it will still capture that ticket, where as that is not actually within that date range. Obviously the reason why is because we are just specifying the Month number as "9" in this case.

In a14

=IFERROR(INDEX($A$2:$A$11,SMALL(IF(($B$2:$B$11>=$I$2)*($B$2:$B$11<=$I$3),ROW($A$2:$A$11)-ROW($A$2)+1,""),ROWS($A$2:A2))),"")

in B14

=IFERROR(INDEX($E$2:$E$11,SMALL(IF(($B$2:$B$11>=$I$2)*($B$2:$B$11<=$I$3),ROW($A$2:$A$11)-ROW($A$2)+1,""),ROWS($A$2:A2))),"")

in A22

=IFERROR(INDEX($A$2:$A$11,SMALL(IF(($F$2:$F$11>=$I$2)*($F$2:$F$11<=$I$3),ROW($A$2:$A$11)-ROW($A$2)+1,""),ROWS($A$2:A2))),"")

in B22

=IFERROR(INDEX($E$2:$E$11,SMALL(IF(($F$2:$F$11>=$I$2)*($F$2:$F$11<=$I$3),ROW($A$2:$A$11)-ROW($A$2)+1,""),ROWS($A$2:A2))),"")

ALL entered with Ctrl+Shift+Enter

Then copy down

Originally Posted by JohnTopley

In a14

=IFERROR(INDEX($A$2:$A$11,SMALL(IF(($B$2:$B$11>=$I$2)*($B$2:$B$11<=$I$3),ROW($A$2:$A$11)-ROW($A$2)+1,""),ROWS($A$2:A2))),"")

in B14

=IFERROR(INDEX($E$2:$E$11,SMALL(IF(($B$2:$B$11>=$I$2)*($B$2:$B$11<=$I$3),ROW($A$2:$A$11)-ROW($A$2)+1,""),ROWS($A$2:A2))),"")

in A22

=IFERROR(INDEX($A$2:$A$11,SMALL(IF(($F$2:$F$11>=$I$2)*($F$2:$F$11<=$I$3),ROW($A$2:$A$11)-ROW($A$2)+1,""),ROWS($A$2:A2))),"")

in B22

=IFERROR(INDEX($E$2:$E$11,SMALL(IF(($F$2:$F$11>=$I$2)*($F$2:$F$11<=$I$3),ROW($A$2:$A$11)-ROW($A$2)+1,""),ROWS($A$2:A2))),"")

ALL entered with Ctrl+Shift+Enter

Then copy down

Awesome ! works great. Thanks.

Thank you the feedback. If you have your required answer, can you mark the thread as SOLVED ("Thread Tools" at top of thread)