Looking up a drop down list entry in a range defined by another drop down list

[AlanClark]

Hi,

I'm working on a spreadsheet that we use to record Supervisor safety contacts (when Supervisors have a "safety moment" with an employee). The contacts fall into different categories/topics (ie: PPE, egonomics, etc.).

Each department (packaging, production, etc.) has a different list of safety contact topics that they consider to be High Risk Topics.

What I'm trying to do is write a function that determines if the topic selected from the drop down list of available topics qualifies as a High Risk Topic, based on the Department selection (another drop down list).

Here is a mock-up of the relevant section:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Employee Department[/TD]
[TD]Topic[/TD]
[TD]High Risk Topic?[/TD]
[/TR]
[TR]
[TD]Drop down list (A)[/TD]
[TD]Drop down list (B)[/TD]
[TD]Yes/No[/TD]
[/TR]
</tbody>[/TABLE]

I have lists of the high risk topics, sorted by department, located on a separate tab.

For example, these are some of the high risk topics for the Packaging department:[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD]Packaging (department)[/TD]
[/TR]
[TR]
[TD]Ergonomics[/TD]
[/TR]
[TR]
[TD]Use of PPE[/TD]
[/TR]
</tbody>[/TABLE]

I get that I could use a COUNTIF or maybe a ISNUMBER(MATCH) function to look up the topic in a list of high risk topics. My issue is how do I change the lookup range based on the department selection.

Essentially I'm trying to write a function that says 'look for topic (B) in the range defined by department (A) and enter yes if there is a match or no if there isn't'.

Thanks for reading my post.

 

[L. Howard]

I think you can do that with a formula like this...

=IF(COUNTIF(INDIRECT(A2),B2),"YES","NO")

Where the Employee Department (value in A2) is a NAMED RANGE and that named range contains the list of "High Risk" items for that department.

So you would have two named ranges (in your shortened example) of Ergonomics and Use of PPE.

They would look like this:
Ergonomics
Use_of_PPE

The under_score is needed to accommodate the spaces in the later.

Howard