Match a drop down list while looking up...

[Jon Tsao]

Hi,

I'm trying to do a look up from a drop down list (sheet 1):

The yellow part is the place where I'm struggling to put in a formula.

The light blue is the drop down list that I'd like to look up. The drop down list contains all the groups in sheet 2 below.

Below is sheet 2, where all the data are:

Now, I need to see if the person belongs to a particular group as I change the drop down (light blue). So for the yellow cell, I'd like to put in a formula that could tell me 'Is Gary a member of group "Everyone"?', so the answer should be 'Yes'. And if I were to change the drop down to 'Volunteer', the yellow cell should show 'No'.

I've tried combination of If/Match/AND but have no luck... any suggestion?

Many thanks.

[#2 · Accepted solution]

Jon.

use COUNTIFS().

=COUNTIFS(Sheet2!$A$2:$A$29,A2,Sheet2!$C$2:$C$29,C2)>0

[#3]

Was thinking there must be a single function that could do that :)

Many thanks!