Lookup from one sheet to another (backwards)

[queysoft]

Hello. I am stuck. I have tried to use INDEX and MATCH but i must be doing it wrong, or there is another solution.

Column F contains words "first, second or third". Column D contains numbers.

On another sheet I am trying to automatically create a row list of all numbers where the word "third" appears in the column F.

Any ideas?

 

[Fluff]

How about like.
In A2 & drag down

=IFERROR(INDEX(Sheet1!$D:$D,AGGREGATE(15,6,ROW(Sheet1!$D$2:$D$200)/(Sheet1!$F$2:$F$200="Third"),ROWS(A$2:A2))),"")

 

[queysoft]

How about like.
In A2 & drag down

=IFERROR(INDEX(Sheet1!$D:$D,AGGREGATE(15,6,ROW(Sheet1!$D$2:$D$200)/(Sheet1!$F$2:$F$200="Third"),ROWS(A$2:A2))),"")

This works well - as needed - Many thanks! Is it possible to add other conditions in for other columns as in bold below? I have tried this but this does not seem to work


=IFERROR(INDEX(Sheet1!$D:$D,AGGREGATE(15,6,ROW(Sheet1!$D$2:$D$200)/(Sheet1!$F$2:$F$200="Third"&Sheet1!$G$2:$G$200="Second"),ROWS(A$2:A2))),"")

 

[Fluff]

For both columns to match you would need

=IFERROR(INDEX(Sheet1!$D:$D,AGGREGATE(15,6,ROW(Sheet1!$D$2:$D$200)/(Sheet1!$F$2:$F$200="Third")/(Sheet1!$G$2:$G$200="Second"),ROWS(A$2:A2))),"")

 

[queysoft]

Wonderful knowledge. Many thanks!

 

[Fluff]

You're welcome & thanks for the feedback.