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

#### Castor

##### New Member
Hey all - may be overthinking this but here goes.

I have two sets of data:

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

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Hi Castor,

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")

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

Hi Castor,

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.

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.

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!

What do you want to do if there's more than one match?

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!

I don't understand where the last four Charge #2 come from?

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)

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.

Thank you for the assistance.

You're welcome.

Replies
6
Views
98
Replies
3
Views
134
Replies
3
Views
194
Replies
3
Views
256
Replies
7
Views
274

1,203,694
Messages
6,056,762
Members
444,890
Latest member
war24

### 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.

### Which adblocker are you using?

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

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