Match Columns from different sheets answer with Yes/No 2

[Marclem]

thread68-1577295

Hi guys,

I need to do the following:

Sheet1 name: AD - Column A

Sheet2 name: Exchange Mail - Column B

I would like to to a search or a look up for matches on both columns and if there are matches, populate AD sheet column D with Yes otherwise with No.

AD sheet

Column A Column D

ABettosini Yes
ABitew No
ABMapunda No
Abongole No
ACabral No
ACabrera Yes
AChilczuk Yes


Exchange Mail

Column B

ABettosini
ACabrera
AChilczuk
aeder
AKita
akwarko
ALeavy

Thanks in advance!

 

[AnotherHiggins]

=if(countif('Exchange Mail'!B:B, A2) > 0, "Yes", "No")

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

FYI, you can simply do this and get TRUE or FALSE...
[tt]
=countif('Exchange Mail'!B:B, A2) > 0
[/tt]
When countif('Exchange Mail'!B:B, A2) is greater than 0, the expression evaluates TRUE. Otherwise the expression evaluates FALSE.

As a practce, using TRUE or FALSE (which is NOT "TRUE" or "FALSE") is better as these are the values that a compute program like Excel uses to evaluate. Anything else must be converted by Excel to TRUE or FALSE.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Marclem]

Thanks! It worked perfectly!

I just have another question. My column D now have Yes and No per the equation sent from you guys above.

Column B
_DC
_Norwalk
Uganda
Ethiopia
Ghana
Interns

Column E: I would like to have yes/no if the following occurs:

If column B shows (_DC, _Norwalk or Interns) and if column D = yes then my column E = No otherwise Yes.

Is this an easy operation to accomplish?

Thanks in advance for your time and support!

 

[SkipVought]

Have you tried anything?

If so, what did you try and what was the result?

If not, why not?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Marclem]

Hi Skip,

I did a search and entered the following:

=IF(COUNTIF(B27,"_DC"),"Yes","No")

That gives me the answer of either Yes or No on my field.

How can I add more variables (_Norwalk, Interns) to check for those words also and then check also for column D as I stated above?

I have searched but still did not find a good equation to solve this problem.

Thanks,

 

[SkipVought]

HUH?

If column B shows (_DC, _Norwalk or Interns) and if column D = yes then my column E = No otherwise Yes.

[tt]
=IF(AND(OR(B27="_DC",B27="_Norwalk",B27="Interns"),D27="yes"),"No","Yes")
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Marclem]

Hi Skip,

Thank you so much!

You have saved me a lot of time here!

=IF(AND(OR(B27="_DC",B27="_Norwalk",B27="Interns"),D27="yes"),"No","Yes")

I just had to change the final ,"No","Yes") to ,"Yes","No") on the ones that had column D as No and it worked fine.