Formula to Compare data from a second Work Sheet

[tbruce]

Hi all,

I'm hoping someone could help me with a formula or perhaps a VLOOKUP. I've been trying different things and can't seem to get where I need to be. What I have is Sheet A with Contacts. Each Contact is associated with a Service Contract on Sheet A. On a second Sheet B, I have a list of all of our Active Service Contracts. What I'm trying to do is on the Contacts Sheet A in Column D, add the word ""active" if the Service Contract name appears on Sheet B in column A. If it exists on Sheet B, I want to place "active" on Sheet A in the column D cell associated with the contact row. Here is a small example of Sheet A and Sheet B:

Sheet A:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]Service Contract[/TD]
[TD]SC Active[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]John[/TD]
[TD]Smith[/TD]
[TD]ABC Plumbing[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Dave[/TD]
[TD]Brown[/TD]
[TD]Brown Down Plumbing[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]Bob[/TD]
[TD]Westra[/TD]
[TD]Ace HVAC and Plumbing[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]Robert[/TD]
[TD]Jones[/TD]
[TD]Roto Rooter[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]Kelly[/TD]
[TD]Palmara[/TD]
[TD]Palms Plumbing[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Sheet B:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Service Contract[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]ABC Plumbing[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Brown Down Plumbing[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]Calipat Plumbing and Heating[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]Central Valley HVAC[/TD]
[TD]Active[/TD]
[/TR]
</tbody>[/TABLE]


Any help would be much appreciated.

Thanks,

Tom

 

[Aladin Akyurek]

Re: Help with Formula to Compare data from a second Work Sheet

In D2 of Sheet1 enter and copy down:

=IF(ISNUMBER(MATCH(C2,Sheet2!A:A,0)),"active","")

where Sheet1 = Sheet A and Sheet2 = Sheet B.

 

[tbruce]

Re: Help with Formula to Compare data from a second Work Sheet

Thank you for the formula it worked, however I made a mistake in the examples. In Sheet2, I also need to confirm in Column B that "Active" is present. If it is blank or has a different entry like "complete" or "pending", then that is equivalent to the account NOT being active, therefore I should not indicate Active on Sheet1. Can you add that to the formula for me - much appreciated

 

[Aladin Akyurek]

Re: Help with Formula to Compare data from a second Work Sheet

Thank you for the formula it worked, however I made a mistake in the examples. In Sheet2, I also need to confirm in Column B that "Active" is present. If it is blank or has a different entry like "complete" or "pending", then that is equivalent to the account NOT being active, therefore I should not indicate Active on Sheet1. Can you add that to the formula for me - much appreciated

Try:

=IFERROR(IF(VLOOKUP(C2,Sheet2!A:B,2,0)="active","active",""),"")

 

[tbruce]

Re: Help with Formula to Compare data from a second Work Sheet

That worked - thanks

 

[Aladin Akyurek]

Re: Help with Formula to Compare data from a second Work Sheet

That worked - thanks

You are welcome.