How to replicate cells if they contain a string in excel

Question

How can I replicate in a new sheet some cells which are in the same column if the cells in the next column contain a certain string?

Let's say I have these two columns and I want to create a new list that contains only the numbers if the word in the second colums is ALPHA:

1   ALPHA
2   BETA
3   ALPHA
4   ALPHA
5   GAMMA
6   DELTA
7   ALPHA

How can I automatically create a new list like this?

1
3
4
7

The list should update automatically, it means that I don't want empty cells among the results of the new list

Answer 1

Supposing your column A is your numbers and column B is your text strings.

A   B 
1   ALPHA
2   BETA
3   ALPHA
4   ALPHA
5   GAMMA
6   DELTA
7   ALPHA

then you can extract the corresponding value to ALPHA using this:

=IFERROR(INDEX($A$1:$B$7,SMALL(IF($B$1:$B$7=$B$1,ROW($A$1:$A$7)),ROW(1:1)),1),"")

This leaves the cell empty if no value found.

P.S. Put this code in any cell and then copy down as many as you want, then go shift+F9.