INDEX/MATCH Help??

[teamplez]

Good evening all. I apologize for just asking for help. I normally muddle through these tasks and figure it out. I have a short fuzed task to pull this together.

I am looking to pull 2 separate lists out of one main list.
Main is the complete "action tracker".
List 1 is what was worked on in the "last 48" hours.
List 2 is what is intended to be worked on in the "Next 48" hours.
One hitch is a task could be in both list 1 and 2, so i added an entry for "last 48, Next 48".

I wanted to keep the column order the same, but it could be changed if it had to be.

I am not exactly sure if index/match is the best solution. I am just under a time crunch and could use some help on solving this puzzle.

Thanks.


MAIN
[TABLE="width: 474"]
<tbody>[TR]
[TD][TABLE="width: 474"]
<tbody>[TR]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Item #[/TD]
[TD]Title[/TD]
[TD]Status[/TD]
[TD]When[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]customer concur[/TD]
[TD]Pending[/TD]
[TD]last 48[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]test solution[/TD]
[TD]Pending[/TD]
[TD]last 48[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]3[/TD]
[TD]implement solution[/TD]
[TD]In Progress[/TD]
[TD]Next 48[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]4[/TD]
[TD]write sop[/TD]
[TD]Completed[/TD]
[TD]last 48, Next 48[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]5[/TD]
[TD]buy beer[/TD]
[TD]Completed[/TD]
[TD]last 48, Next 48[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]6[/TD]
[TD]read book[/TD]
[TD]In Progress[/TD]
[TD]Next 48[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]7[/TD]
[TD]training[/TD]
[TD]Pending[/TD]
[TD]last 48[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Formula to generate "last 48" list
=INDEX($B$4:$B$10,MATCH($I$3,D$4:$D$10, 0),1)

Please recommend other solutions outside of index/match if you can think of something different.

Thanks much!!!

 

[taurean]

With your current setup you can use following formula which should be committed by pressing CTRL+SHIFT+ENTER (array formula) simultaneously. Then copy down as much as you need.
=IFERROR(INDEX($B$1:$B$8,SMALL(IF(ISNUMBER(SEARCH($I$3,$D$1:$D$8)),ROW($D$1:$D$8),9E+307),ROWS($A$1:A1))),"")

 

[teamplez]

Good Morning --- (Probably Afternoon in Oman)

Shrivallabha - Works like a charm. Appreciate the quick response.

 

[taurean]

Great! Thanks for the feedback.