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

L

lefty29er

New Member
Joined
Sep 10, 2011
Messages
5
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
 
Sort by date Sort by votes
lefty29er said:
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.
Click to expand...
You should post the entire formula.

Here's a tweak to shg's formula:

=SUM(COUNTIFS(A:A,2025551234,B:B,{2023331234,2024445678}))>0
 
Upvote 0

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
lefty29er said:
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
Click to expand...

=SUM(COUNTIFS(A:A,2025551234,B:B,{2023331234,2024445678}))>0

If you want to do it per record/row:

=(A2=2025551234)*OR(B2={2023331234,2023334567})>0
 
Upvote 0
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><COL><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><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</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><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">NumberA1</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">NumberA2</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">NumberB1</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">NumberB2</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><TD style="TEXT-ALIGN: center">2025551234</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">2023331234</TD><TD style="TEXT-ALIGN: center">2023334567</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><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></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><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></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><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: center">*******</TD><TD style="TEXT-ALIGN: center">*******</TD><TD style="TEXT-ALIGN: center">*******</TD><TD style="TEXT-ALIGN: center">*******</TD><TD style="TEXT-ALIGN: center">*******</TD><TD style="TEXT-ALIGN: center">*******</TD><TD style="TEXT-ALIGN: center">*******</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(--OR(A2=D$2:D$2),--OR(B2=F$2:G$2)),"True","False")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Markmzz
 
Upvote 0
lefty29er said:
There's a bit more to the formula than what I'm posting ...
Click to expand...
I'm also wondering what else is involved, but another way to identify Calling/Called number combinations would be to use Advanced Filter (on the Data ribbon tab).

With a setup like this. That is
- main data in rows 6:13
- criteria in rows 1:3

Excel Workbook
AB
1CallingCalled
220255512342023331234
32024445678
4
5
6CallingCalled
720255512342023334567
820255545672023331234
920255545672023334567
1020255512342023331234
1120255545672023334567
1220255512342024445678
1320255545672023334567
14
Advanced Filter



Then invoke the Advanced Filter dialog and ..
- Filter the list, in-place
- List range: A6:B13
- Criteria range: A1:B3
- OK

Ther result is:

Excel Workbook
AB
1CallingCalled
220255512342023331234
32024445678
4
5
6CallingCalled
1020255512342023331234
1220255512342024445678
14
Advanced Filter
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
MATCH and INDEX help
Replies
3
Views
805
etaf
etaf
L
  • Solved
Repeat formula through column X times
Replies
9
Views
240
Fluff
Fluff
R
  • Solved
index match question - search column x for a value matching y
Replies
2
Views
254
RaviWildcat
R
U
  • Question Question
Dynamic Macro for INDEX/MATCH Formula
Replies
1
Views
361
StephenCrump
S
M
  • Solved
Having issues with a IF(MAX(INDEX formula returning Yes or No
Replies
3
Views
277
RoryA
RoryA

Forum statistics

Threads
1,223,723
Messages
6,174,122
Members
452,545
Latest member
boybenqn

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top