Previous Topic Next Topic

Excel compare and return result

Closed
Report
Posts
2
Registration date
Thursday July 12, 2018
Status
Member
Last seen
July 12, 2018
 -
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
 -
Hello,

I have a set of field to compare in 2 different spreadsheet out of which any match will then check and return for a specific value in another column.

E.g. Compare the name and if the role exist for the name, return yes. If not, return no.

Spreadsheet A:

Name Role
Mary Secretary
Mary HOD
Jane Secretary
Jane HOD
Jane Admin

Spreadsheet B:

Name Secretary Admin
Mary <Return Yes> <Return No>
Jane <Return Yes> <Return Yes>

How do I achieve the <Return Yes/No> part in Spreadsheet B?

Thanks!


2 replies

Answer 1 / 2
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
 53
Hi newbie881,

One quick question: Mary and Jane are repeated in the first spreadsheet, does your data have repeated names like this?
Report
Posts
2
Registration date
Thursday July 12, 2018
Status
Member
Last seen
July 12, 2018
Yes! 1 person is assigned to multiple roles.
Answer 2 / 2
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
 53
Hi newbie881,

I am sorry for the delayed response, please check this formula - it should work:


Formula for Secretary Column: =IF(COUNTIFS([Book1]Sheet1!$B:$B,$B$1,[Book1]Sheet1!$A:$A,A2),"Yes","No")
Formula for Admin Column: =IF(COUNTIFS([Book1]Sheet1!$B:$B,$C$1,[Book1]Sheet1!$A:$A,A2),"Yes","No")

Please do write back to me, if you need any further clarification about these formulas.
Ask a question