Pull list of entries with a match in another column

[virtuosok]

Hi,
I have the data in the following format:

What I need to do is to pull all numbers from column E (there are no duplicates there) whenever there's a "Randomized" tag in respective row, column G. I.e. I need to pull 197, 198, 199 (but not 200), then 201 etc. This is a dynamic table so filtering by "randomized" is not practical unfortunately. I'd much rather have these numbers on a separate tab - one number per cell, auto-refreshing whenever there's new source data.
Thanks for the advice!

 

[virtuosok]

update... looks like solution is found for the first entry (cell A2 now contains "Randomized" as a fixed reference)

=INDEX(IWRS!E:E,SMALL(IF($A$2=IWRS!G:G,ROW(G:G),""),ROW()-ROW(B2)))

...but when pulling down, I'm getting the same number again and again.

 

[virtuosok]

If there's no solution for Excel 2016, appreciate a hint for 365 version...

 

[Peter_SSs]

For Excel 2016 try this in B2, copied down. Note that I have not used whole column references as that can lead to a significant calculation overhead which is generally not warranted. Just use a big enough row range that it will always cover any expected data.

=IFERROR(INDEX(IWRS!E$1:E$1000,AGGREGATE(15,6,(ROW(IWRS!E$1:E$1000)-ROW(IWRS!E$1)+1)/(IWRS!G$1:G$1000=A$2),ROWS(B$2:B2))),"")

If you did have Excel 365 it would be much simpler as you would only need this simple formula in B2 and not even need to copy it down.

=FILTER(IWRS!E1:E1000,IWRS!G1:G1000=A2,"")

 

[virtuosok]

Thank you Peter_SSs!
The 2016 formula works perfectly! I was already thinking about posting the file on Teams and apply FILTER function there (as I don't have "offline" Office 365 yet).
Have a great day!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.