Extract from list in column A based on cell value in column B

[newbie-excel]

Hello, I would like to know how I can extract values from one column depending on the cell value in another column in a table.

The data will need to be extracted into another workbook.

For example:

Sheet1:

A______________B
Apple__________Diamond
Mango_________Coal
Orange________Platinum
Banana________Diamond
Pear___________Diamond
Kiwi____________Coal
Grape__________Platinum

Now in another workbook I would like a list of all column A cells where the corresponding column B cell is Diamond, so column A should look like this....

A
Apple
Banana
Pear

Preferably I don't want to use advanced filter.

[Fotis1991]

Try in A2 of your second sheet this ARRAY formula. Copy down.

=INDEX(Sheet1!$A$2:$A$100,SMALL(IF(Sheet1!$B$2:$B$100="Diamont",ROW(Sheet1!$A$2:$A$100)-1),ROW(Sheet1!A1)))

=IFERROR(INDEX(Sheet1!$A$2:$A$100,SMALL(IF(Sheet1!$B$2:$B$100="Diamont",ROW(Sheet1!$A$2:$A$100)-1),ROW(Sheet1!A1))),"")



-- Array(CSE) { }, formulae are confirmed with Control+Shift+Enter.
Not just Enter.

[Fotis1991]

Thank you for the rep*.

As i understand, that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thank you.