CODEWITHSUNDEEP
excelpivot-table
Yossale
Yossale

Reputation: 14361

Looking multiple values on multiple columns in excel

I have a table where each person has several "Job" columns. I need to find all the employees who has a specific value ("Actor") in one of their "Job" columns.

I thought about doing HLookup on multiple columns, but Lookup functions only returns the first match (and I'm not sure I can use it on multiple columns). I also tried Pivot Tables, but all I got is aggregation, not the exact matches. How can I solve it?

For example, from the sample data below, when looking for "Actor", I would like to get both "John, Doe" and "Todd, Dude"

Sample data:

Id | First Name | Last Name | email     | Job1   |     Job2     |   Job3   | Job4
-----------------------------------------------------------------------------------
1  | John       | Doe       | [email protected]  | Actor  | Photographer | Producer | 
2  | Todd       | Dude      | [email protected]  | Lights | Actor        |          | 
3  | Janis      | Joplin    | [email protected]  | Singer |              |          |

Upvotes: 0

Views: 115

Answers (2)

XOR LX
XOR LX

Reputation: 7762

Assuming the table as you give it is in A1:H4 (with headers in row 1), and that you put e.g. "Actor" in J1, this array formula** in J2:

=IFERROR(INDEX($B$2:$B$4&" "&$C$2:$C$4,SMALL(IF($E$2:$H$4=J$1,ROW($E$2:$H$4)-MIN(ROW($E$2:$H$4))+1),ROWS($1:1))),"")

Copy down until you start to get blanks. The formula may also be copied across to give results for other professions listed in K1, L1, etc.

Regards

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Upvotes: 1

apc
apc

Reputation: 81

I have no idea, how to it in one formula. All lookups and match returns only first reference. You could add column jobActor --- true if one of the jobs is Actor and then create pivot --- filter on jobAll, row names is person names.

Maybe advanced filter could be the way how to do it.

Upvotes: 1

Related Questions