Forum Discussion

bepsky's avatar
bepsky
Copper Contributor
Jun 17, 2019

Excel Formula Help - Cross Referencing Two Spreadsheets

I'm looking to build a formula that will essentially get two spreadsheets in-sync.  Both spreadsheets contain vast entries/rows that have "Names", "Dates" & "Types".  I would like to build a formula into spreadsheet #2 that will let me know if the corresponding row/entry appears in spreadsheet #1.  What functions are needed to make this work?  I would like for the formula to match the exact "Name", "Date" & "Type" in spreadsheet #1 and to spit out a "Yes" (if it's found on spreadsheet #1) or "No".

 

The multiple arguments (needing to reference the name, date & time exactly) is what I believe is giving me the most trouble in figuring this out.  Any help would be very much appreciated!

developer
excel
Excel Online
Formulas and Functions
training

3 Replies

  • Twifoo's avatar
    Twifoo
    Silver Contributor
    Jun 19, 2019

    bepsky 

    In the attached file, the formula in Sheet2!D2 is: 

    =IF(COUNTIFS(Sheet1!A:A,A2,
    Sheet1!B:B,B2,
    Sheet1!C:C,C2),
    "Yes","No")

    The foregoing formula counts the instances when the 3 conditions are met and returns "Yes" if the count is 1 or more; otherwise, it returns "No".

Resources