Lookup value - see if exists in another array

[mkondo]

Hello - hoping someone can help me out.

I need to look at a value in one column and see if it is in a list in
another column depending on the column header.

Eg:

A B C
1Category Name Value
2 x john
3 y bob
4 z jane
5 x mary
6 x bob

I need to return 1 if true and 0 if false dependent on lookups in a
separate sheet:

A B C
1 x y z
2 john bob jane
3 mary

So the answer would be
C2= 1
C3= 1
C4= 1
C5=1
C6=0

Many thanks
Megan

 

[Guest]

With
Your lookup table structure on Sheet1 in cells A1:C10
and
this list on Sheet2, cells A1:C6
Category Name Value
x john
y bob
z jane
x mary
x bob


Try this on Sheet2:
C2: =SUMPRODUCT((Sheet1!$A$1:$C$1=A2)*(Sheet1!$A$2:$C$10=B2))

If there could only be one possible match, the formula will return 1.
Otherwise, it returns zero

If ther could be more than one match....use this, instead:
C2: =--(SUMPRODUCT((Sheet1!$A$1:$C$1=A2)*(Sheet1!$A$2:$C$10=B2))>0)

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP