cross-reference multiple criteria? - index/match, If/and?

Castor

New Member
Joined
Mar 20, 2019
Messages
20
Office Version
  1. 2016
Platform
  1. Windows
Hey all - may be overthinking this but here goes.

I have two sets of data:

1642112666429.png



What I am trying to do is return either a 'yes' or 'no' value in column H if...

Both B5 and C5 together match the same values together somewhere within Data Set 2.

I hope I explained well enough.

Any help is much appreciated!
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Hi Castor,

Please test this.

Castor.xlsx
BCDEFGH
4ID1Date1ID2Date2Yes or No
512302-Feb-2278916-Jan-22No
612304-Jan-2245613-Jan-22Yes
712307-Jan-2245610-Jan-22No
845610-Jan-2222210-Jan-22Yes
945613-Jan-2299907-Jan-22Yes
1078902-Feb-2212304-Jan-22No
1178919-Jan-2212301-Jan-22No
Sheet1
Cell Formulas
RangeFormula
H5:H11H5=IF(COUNTIFS($E$5:$E$99,B5,$F$5:$F$99,C5)>0,"Yes","No")
 
Upvote 0
Solution
Try this:

Excel Formula:
IF(COUNTIFS($E$5:$E$18,B5,$F$5:$F$18,C5)>0,"Yes","NO")

Let me know if this works
 
Upvote 0
Hi Castor,

Please test this.

Castor.xlsx
BCDEFGH
4ID1Date1ID2Date2Yes or No
512302-Feb-2278916-Jan-22No
612304-Jan-2245613-Jan-22Yes
712307-Jan-2245610-Jan-22No
845610-Jan-2222210-Jan-22Yes
945613-Jan-2299907-Jan-22Yes
1078902-Feb-2212304-Jan-22No
1178919-Jan-2212301-Jan-22No
Sheet1
Cell Formulas
RangeFormula
H5:H11H5=IF(COUNTIFS($E$5:$E$99,B5,$F$5:$F$99,C5)>0,"Yes","No")

Hi there,

Yes! It seems to be working! Thank you so much. Will post back if I run into any issues.
 
Upvote 0
This solution has worked like a charm, however I now need to build on it.

Further in the data now is a charge amount. I was hoping for a way to pull the corresponding charge amount that matches to the multiple criteria set forth from the original question.

1643144982066.png



So basically after matching the criteria set forth in the first formula provided, i was hoping to be able to next pull in the charge amount from Charge#2 into the corresponding row for Charge #1.

Please let me know if this is not clear.

Thank you so much!
 
Upvote 0
What do you want to do if there's more than one match?
 
Upvote 0
What do you want to do if there's more than one match?
Thank you for the reply. I don't think there should be more than one, unless I am misunderstanding you.

I am matching the ID and date in Set#1 with the ID and date in Set#2, and then pulling in that corresponding charge from Set#2 into the Charge #1 column.

Let me know if I am misunderstanding, expertise is greatly appreciated!
 
Upvote 0
I don't understand where the last four Charge #2 come from?

1643213497178.png


If there's no duplicates then you can do it with a SUMIFS,assuming those last for Charge #2 numbers are further down the sheet.

Castor.xlsx
BCDEFGHI
4ID1Date1Charge #1ID2Date2Yes or NoCharge #2
5P13057cc03-Sep-20-P130575zz21-Dec-20Yes$300
6P130579bb02-Sep-20-P130577zz28-Dec-20Yes$150
7P130579bb03-Sep-20-P130580yy17-Dec-20Yes$200
8P130579aa23-Dec-20-P130581yy01-Sep-20Yes$500
9P130575zz21-Dec-20$300206066xxx14-Sep-20No-
10P130577zz28-Dec-20$150206066xxx01-Oct-20No-
11P130580yy17-Dec-20$200206066xxx23-Oct-20No-
12P130581yy01-Sep-20$5008743534yy22-Feb-21No-
2nd
Cell Formulas
RangeFormula
H5:H12H5=IF(COUNTIFS($B$5:$B$9999,F5,$C$5:$C$9999,G5)>0,"Yes","No")
I5:I12I5=SUMIFS($D$5:$D$9999,$B$5:$B$9999,F5,$C$5:$C$9999,G5)
 
Upvote 0
I don't understand where the last four Charge #2 come from?

View attachment 56198

If there's no duplicates then you can do it with a SUMIFS,assuming those last for Charge #2 numbers are further down the sheet.

Castor.xlsx
BCDEFGHI
4ID1Date1Charge #1ID2Date2Yes or NoCharge #2
5P13057cc03-Sep-20-P130575zz21-Dec-20Yes$300
6P130579bb02-Sep-20-P130577zz28-Dec-20Yes$150
7P130579bb03-Sep-20-P130580yy17-Dec-20Yes$200
8P130579aa23-Dec-20-P130581yy01-Sep-20Yes$500
9P130575zz21-Dec-20$300206066xxx14-Sep-20No-
10P130577zz28-Dec-20$150206066xxx01-Oct-20No-
11P130580yy17-Dec-20$200206066xxx23-Oct-20No-
12P130581yy01-Sep-20$5008743534yy22-Feb-21No-
2nd
Cell Formulas
RangeFormula
H5:H12H5=IF(COUNTIFS($B$5:$B$9999,F5,$C$5:$C$9999,G5)>0,"Yes","No")
I5:I12I5=SUMIFS($D$5:$D$9999,$B$5:$B$9999,F5,$C$5:$C$9999,G5)
My apologies, I should have been more clear - those values were put there as an example because they match the ID/Date values for 4 of the entries in the data sets.

Your solution is working perfectly.

Thank you for the assistance.
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,344
Members
448,570
Latest member
rik81h

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