Return "Yes"/"No" when column A = x AND B = y

[lefty29er]

Hello,

My first post here on Mr. Excel and total Excel noob. I'm trying to create a formula where it will return a yes/no when looking up phone numbers in 2 different columns to see if they exist together within the same row. The gist is that I have call detail records where column A is the calling party and column B is the called party. I also have a list of calling numbers that should have dialed a known phone number. Here's an example:

ColumnA ColumnB
2025551234 2023334567
2025554567 2023331234
2025554567 2023334567
2025551234 2023331234

I would like to identify if there is a row where column A is 2025551234 and column B is 2023331234, which would be row 5 here. I've used some index/match formulas but can't get past the limitation of the formula finding the first instance of my calling number and indicating yes/no. If no, I'd like to keep searching down to find if it exists below which in this case would be the last row. I appreciate any assistance and please let me know if you need more/better/clearer info.

Thanks,

Lefty29er

 

[shg]

This will telll you if it exists:

=countifs(a:a, 2025551234 , b:b, 2023331234)

 

[markmzz]

Another way:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">ColumnA</td><td style="text-align: center;;">ColumnB</td><td style="text-align: center;;">Result</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">2025551234</td><td style="text-align: center;;">2023334567</td><td style="text-align: center;;">No</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">2025554567</td><td style="text-align: center;;">2023331234</td><td style="text-align: center;;">No</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">2025554567</td><td style="text-align: center;;">2023334567</td><td style="text-align: center;;">No</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">2025551234</td><td style="text-align: center;;">2023331234</td><td style="text-align: center;;">Yes</td></tr></tbody></table><p style="width:4.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet10</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=IF(<font color="Blue">SUMPRODUCT(<font color="Red">--(<font color="Green">A2:B2={2025551234,2023331234}</font>)</font>)=2,"Yes","No"</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[lefty29er]

WOW! I am blown away at fast and helpful replies. @SHG - worked like a charm - thank you. @MarkMZZ - thank you for the second way to skin this cat . I'll certainly be trying that one as well and I'm sure it will do the trick. I really wanted to get this one on my own, but I'm glad there's help out there when it's needed. Have a great day!

 

[shg]

You're welcome, glad it helped.

 

[markmzz]

WOW! I am blown away at fast and helpful replies. @SHG - worked like a charm - thank you. @MarkMZZ - thank you for the second way to skin this cat . I'll certainly be trying that one as well and I'm sure it will do the trick. I really wanted to get this one on my own, but I'm glad there's help out there when it's needed. Have a great day!

Lefty29er,

I'm happy that your problem was solved.

This that is important.

And have a great day too.

Markmzz

 

[lefty29er]

This will telll you if it exists:

=countifs(a:a, 2025551234 , b:b, 2023331234)

SHG,

I've been using your formula here with great success, thanks again. I have an additional challenge of looking to see if one of multiple numbers exist for criteria 1. I'm putting an "OR" step in to check for multiple numbers but it doesn't seem to be finding it. Here's an example of my formula:

=if(countifs(a:a, 2025551234, b:b,(or(2023331234,2024445678)),"True","False")

I get a false, even when what I'm trying to accomplish is true. Any help is appreciated.

Thanks

 

[shg]

Maybe this:

=IF(SUM(COUNTIFS(A:A, 2025551234,B:B, {2023331234,2024445678})), TRUE, FALSE)

 

[lefty29er]

Thanks shg. I think that's going to work since I can see that the criteria item remains the same through the commas once I add in the {}, but I'm getting a formula error once they're added in. There's a bit more to the formula than what I'm posting so I'll keep working on it and will post back with an update.

 

[markmzz]

Another way:

Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">ColumnA</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">ColumnB</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Result</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: center">2025551234</TD><TD style="TEXT-ALIGN: center">2023334567</TD><TD style="TEXT-ALIGN: center">True</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: center">2025554567</TD><TD style="TEXT-ALIGN: center">2023331234</TD><TD style="TEXT-ALIGN: center">False</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: center">2025554567</TD><TD style="TEXT-ALIGN: center">2023334567</TD><TD style="TEXT-ALIGN: center">False</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: center">2025551234</TD><TD style="TEXT-ALIGN: center">2023331234</TD><TD style="TEXT-ALIGN: center">True</TD></TR></TBODY></TABLE>Sheet1


<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C2</TH><TD style="TEXT-ALIGN: left">=IF(SUMPRODUCT((A2=2025551234)+OR(B2={2023331234,2023334567}))=2,"True","False")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Markmzz