 Highlighted

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

3 Replies
Highlighted

# Re: Excel Formula Help - Cross Referencing Two Spreadsheets

For such sample it could be

`=IF(ISNUMBER(MATCH(1,INDEX((Sheet1!\$A:\$A=\$A2)*(Sheet1!\$B:\$B=\$B2)*(Sheet1!\$C:\$C=\$C2),0),0)),"Yes","No")`
Highlighted

# Re: Excel Formula Help - Cross Referencing Two Spreadsheets

Hi

Try 3D referencing. Hope that helps.

Highlighted

# Re: Excel Formula Help - Cross Referencing Two Spreadsheets

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