Compare data from two different worksheets with a condition

[bseeley]

I have two worksheets in the same workbook. I want to compare two columns
from each sheet, both labeled "Case ID", and if matching ID's are found then
compare another column for the same row labeled "Frequency". If the ID's
match and the frequencies match, then I would want it to return the word
"yes" in a different column (e.g. column F). If the frequencies are
different, then I would want to return the frequency from the first
worksheet. If there isn't a matching ID found, then it would leave the cell
in column F of that row blank.

For example,

Worksheet A Worksheet B
C H C H
Case ID Frequency Case ID Frequency
1596-5218 Monthly 1596-5218 Every 2 Months
1052-1012 Every 6 months 1213-0763 Monthly
0250-2845 Monthly 0250-2845 Every 3 Months
0143-7778 Every 3 months 0931-5110 Every 2 Months


And return to Worksheet A

C F
1596-5218 Yes
1052-1012
0250-2845 Monthly
0143-7778

Please help! Thanks very much

 

[Jim Thomlinson]

untested but this should be close...

=IF(ISNA(MATCH(C2,SheetB!$C$2:$C$100,0)),"",IF(INDEX(SheetB!$H$2:$H$100,MATCH(C2,SheetB!$C$2:$C$100,0))=H2,
"Yes", INDEX(SheetB!$H$2:$H$100,MATCH(C2,SheetB!$C$2:$C$100,0))))

 

[bseeley]

Thanks very much for the help.

I tried the formula and get the result of the ID number found in Column C,
cell 18 (using the row from my actual worksheet) of the first worksheet (I
call it "Compliance"). I attempted to modify the formula, because I believe
it should, at some point, refer to the second sheet that contains the other
column of ID numbers that I want to compare with. This was my attempt:

=IF(ISNA(MATCH(C18,Compliance!$C$18:$C$2817,0)),"",IF(INDEX(Compliance!$C$18:$C$2817,MATCH(C18,DataMay09!C18:C2838,0))=H18,"Yes",
INDEX(Compliance!$C$18:$C$2817,MATCH(C18,DataMay09!$C$18:$C$2838,DataMay09!!))))

This doesn't work, and I don't know what else to try that might fix it.
Please provide me with more assistance. Thank you very much!