Excel Choosing Column based on filter criteria of other column

Question

Name   Skill
Mike  Engineer
Salom  Doctor
Riku   Labour
Sindu  Engineer

We need to select who all are engineers or doctors

Answer 1

You should accept @SardarUsama's solution as answer because it answers your actual question. If your requirement changes/extends or you were not able to put your question correctly to others then it is advisable that you accept the provided solution and then ask new question instead of making people change answer number of times.

Now, coming to your question (as per comment shared between you and @SardarUsama's) following might be helpful.

Enter the following formula in Cell C2

=IFERROR(INDEX($A$2:$A$10, SMALL(IF(($B$2:$B$10="Engineer")+($B$2:$B$10="Doctor"), ROW($A$2:$A$10)-1, ""), COLUMN(B$1)-COLUMN($A$1))),"")

This is an array formula so commit it by pressing Ctrl+Shift+Enter

Or else you can use another array formula

=IFERROR(INDEX($A$2:$A$10, SMALL(IF($B$2:$B$10={"Engineer","Doctor"}, ROW($A$2:$A$10)-1, ""), COLUMN(C$1)-COLUMN($B$1))),"")

Drag/Copy across as required. See image for reference.

Answer 2

Use COUNT and SEARCH. If D2 is the cell you're searching in then:

=COUNT(SEARCH({"Doctor","Engineer"},D2))

Drag this formula downwards to apply to all rows.

---

Edit: As you now mentioned that you want to print the names in the Result column, combine the above formula with IF like this:

=IF(COUNT(SEARCH({"Doctor","Engineer"},D2)),C2,"")

where D2 is the cell that you're searching in and C2 is the cell that you want in the result cell if Doctor or Engineer exists in the cell D2. Dragging the formula downwards to apply to all rows gives: