look up question

  • Thread starter Thread starter J Donahue
  • Start date Start date
J

J Donahue

I have a list of people with their system rights in the
next column by group. Each person may have more than one
type of right in the list as follows

John Smith Edit
Joe Doe Submit
Cathy Dory Edit
John Smith Submit

As you can see above, John Smith has both edit and submit
rights on this list. What I am looking for is a formula
where I can look down the list of names and find all
instances of John Smith and then it can tell me if John
Smith ever has edit rights or ever has submit rights.
What I would like it to do is return a 1 if he has edit
rights and a 0 if he does not, and then a I will put a
similar formula to return a 1 if he has submit rights and
a 0 if he does not.

Can anyone tell me if this is possible?

Thanks in advance

J Donahue
 
Hello J;
Let your table be in A1:B4
In D1 enter person's name, and in E1 enter a right (Edit, Submit...)
In D2/E2 another person/right
In F1 enter =SUMPRODUCT(--($A$1:$A$4=D1),--($B$1:$B$4=E1))
Copy F1 down to 'catch' all data in column D.

Of course, you can put the table on another sheet and use
=SUMPRODUCT(--(Sheet2!$A$1:$A$4=D1),--(Sheet2!$B$1:$B$4=E1))

Bernard
 
With the Data in A2:B5

=IF(SUMPRODUCT((A2:A5="John Smith")*(B2:B5="Edit"))>0,1,0)

Will return 1 if John Smmith has edit rights, 0 if he has not. It will be
easier if you have a seperate list of unique names and
edit rights, then with John Smith in say D1 and Edit in E1

=IF(SUMPRODUCT((A2:A5=D1)*(B2:B5=E10,1,0)

will give the same result.
 
Thank you guys for the help, the sumproduct formula worked
perfectly for what I needed.

J Donahue
 
=IF(SUMPRODUCT((A2:A5=D1)*(B2:B5=E10,1,0)

will give the same result.

No it wont, this will, sorry!!!

=IF(SUMPRODUCT((A2:A5=D1)*(B2:B5=E1))>0,1,0)
 
Back
Top