Excel Formula Help - Cross Referencing Two Spreadsheets

Copper Contributor

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

@bepsky 

 

For such sample

image.png

it could be

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

@bepsky 

 

Hi 

 

Try 3D referencing. Hope that helps.  

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