Multiple Matches and If Statement

Hello All,

See attached. In Sheet3, Column BC, I am trying to input a formula that will return the value in Sheet2 column J based on the following match:

Match 1
Sheet3 column K matches Sheet2 column F

Match 2
Sheet3 column CD matches Sheet2 column B

Match 3
Once the above matches have been made, I need the formula to search for "CONLAB" "FULLSC" and "INST" In Sheet2 Column G. If it finds one of these three for the corresponding site ID, it should return the info in row J.

Caveat
If it makes all of the above matches but there is a duplicate entry (see Sheet2 rows 8/9. The Site # is listed twice), it should return the word “Duplicate”.

Does this make sense?

I don't understand the "Duplicate" rules - aren't rows 2/3 on sheet2 duplicates because they have the same Site#? Same with rows 4/5?

Using the helper column on Sheet2, column D to identify duplicates makes the formula easier. Once that column identifies duplicates, you can use the formula below in Sheet3 BC2 to return the appropriate match response. The formula must be array-entered (confirm with Ctrl + Shift + Enter instead of Enter) in BC2:

=IFERROR(IFERROR(INDEX(Sheet2!$D$1:$D$1000, MATCH(1,(Sheet2!$D$1:$D$1000="Duplicate")*($K2=Sheet2!$F$1:$F$1000)*($CD2=Sheet2!$B$1:$B$1000)*((Sheet2!$G$1:$G$1000="CONLAB")+(Sheet2!$G$1:$G$1000="FULLSC")+(Sheet2!$G$1:$G$1000="INST")),0)),INDEX(Sheet2!$J$1:$J$1000, MATCH(1,($K2=Sheet2!$F$1:$F$1000)*($CD2=Sheet2!$B$1:$B$1000)*((Sheet2!$G$1:$G$1000="CONLAB")+(Sheet2!$G$1:$G$1000="FULLSC")+(Sheet2!$G$1:$G$1000="INST")),0))),"")

You can then fill it down as far as you need to. In my attachment, the results are based on my current interpretation of "Duplicates" on sheet 2. If I'm misunderstanding things, all that needs to be adjusted is the formula for duplicates in sheet2, column D. Currently, I'm using:

=IF(COUNTIF($B$2:$B$1000,$B2)>1,"Duplicate","")

If I've got it wrong, please fill me in on what I'm missing about the rules for duplicates and I should be able to help tweak the formula (if necessary).

Originally Posted by CAntosh

I don't understand the "Duplicate" rules - aren't rows 2/3 on sheet2 duplicates because they have the same Site#? Same with rows 4/5?

Using the helper column on Sheet2, column D to identify duplicates makes the formula easier. Once that column identifies duplicates, you can use the formula below in Sheet3 BC2 to return the appropriate match response. The formula must be array-entered (confirm with Ctrl + Shift + Enter instead of Enter) in BC2:

=IFERROR(IFERROR(INDEX(Sheet2!$D$1:$D$1000, MATCH(1,(Sheet2!$D$1:$D$1000="Duplicate")*($K2=Sheet2!$F$1:$F$1000)*($CD2=Sheet2!$B$1:$B$1000)*((Sheet2!$G$1:$G$1000="CONLAB")+(Sheet2!$G$1:$G$1000="FULLSC")+(Sheet2!$G$1:$G$1000="INST")),0)),INDEX(Sheet2!$J$1:$J$1000, MATCH(1,($K2=Sheet2!$F$1:$F$1000)*($CD2=Sheet2!$B$1:$B$1000)*((Sheet2!$G$1:$G$1000="CONLAB")+(Sheet2!$G$1:$G$1000="FULLSC")+(Sheet2!$G$1:$G$1000="INST")),0))),"")

You can then fill it down as far as you need to. In my attachment, the results are based on my current interpretation of "Duplicates" on sheet 2. If I'm misunderstanding things, all that needs to be adjusted is the formula for duplicates in sheet2, column D. Currently, I'm using:

=IF(COUNTIF($B$2:$B$1000,$B2)>1,"Duplicate","")

If I've got it wrong, please fill me in on what I'm missing about the rules for duplicates and I should be able to help tweak the formula (if necessary).

I'm having a hell of a time with this. Although I was able to get it to work in the sample doc, I wasn't able to get it to work in my sheets! Sigh. I've attached my sheets. Can you look over the formulas to see if I am doing anything wrong?

There was a minor tweak to make to one of the INDEX ranges, but the bigger issue was that your site numbers on XXX XRM are regarded as text, whereas the site numbers on tracker are considered numbers. I added a "+0" to the formula to massage the text entries into numbers, so the new version should work even with the mismatched formatting. Array-entered in AO2:

=IFERROR(IFERROR(INDEX('XXX XRM'!$J$1:$J$404, MATCH(1,('XXX XRM'!$J$1:$J$404="Duplicate")*($L2='XXX XRM'!$F$1:$F$404)*($CD2='XXX XRM'!$A$1:$A$404+0)*(('XXX XRM'!$G$1:$G$404="INST")+('XXX XRM'!$G$1:$G$404="FULLSC")+('XXX XRM'!$G$1:$G$404="CONLAB")),0)),INDEX('XXX XRM'!$L$1:$L$404, MATCH(1,($L2='XXX XRM'!$F$1:$F$404)*($CD2='XXX XRM'!$A$1:$A$404+0)*(('XXX XRM'!$G$1:$G$404="INST")+('XXX XRM'!$G$1:$G$404="FULLSC")+('XXX XRM'!$G$1:$G$404="CONLAB")),0))),"")

I don't think you need two columns for duplicates on XXX XRM, either. The helper column I mentioned in my earlier post was a reference to the duplicates column you already had. The formula only checks in column J of XXX XRM for the term "Duplicate", then acts accordingly, so you can probably get rid of column I and just use whatever formula you need to identify duplicates in column J.

Great work!!!! SOLVED

Thank you, good luck!

Originally Posted by CAntosh

Thank you, good luck!

Hello Cantosh,

Match 3
Once the above matches have been made, I need the formula to search for "CONLAB" "FULLSC" and "INST" In Sheet2 Column G. If it finds one of these three for the corresponding site ID, it should return the info in row J.

I am trying to edit the last formula you sent me for it to only search for CONLAB instead of CONLAB FULLSC and INST. I've tried editing the formula but its not working. Can you take a crack at it for me?