As the title reads, I am trying to extract a list of data from another list of data that meets specific criteria.
For instance, the "original data" may have 100 lines of data with the following columns - "Name" "Date" "Action" "Comment" "Status".
On a different sheet, I want to show the (1) most recent 10 data that (2) has Name "ABC" and (3) Status is "Done".
I thought I had this working by using
{=LARGE(IF('Sheet1'!$A$2:$A$500=$E$3,IF('Sheet 1'!$H$2:$H$500="Done",'Sheet 1'!$F$2:$F$500)),11-ROW(A1))} for "Date"
and
{=INDEX('Sheet 1'!B$2:B$500,MATCH($E$2&$F25,'Sheet 1'!$A$2:$A$500&'Sheet 1'!$F$2:$F$500,0))}
However, I realized that when there are more than one same values for "Date", the second formula does not work. Please refer to the attachment (highlighted parts).
I'm thinking I need a different approach to this. I would appreciate any suggestions.
Thanks!
Bookmarks