Home

Excel Formula Help - Cross Referencing Two Spreadsheets

%3CLINGO-SUB%20id%3D%22lingo-sub-698119%22%20slang%3D%22en-US%22%3EExcel%20Formula%20Help%20-%20Cross%20Referencing%20Two%20Spreadsheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-698119%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20looking%20to%20build%20a%20formula%20that%20will%20essentially%20get%20two%20spreadsheets%20in-sync.%26nbsp%3B%26nbsp%3BBoth%20spreadsheets%26nbsp%3Bcontain%26nbsp%3Bvast%26nbsp%3Bentries%2Frows%20that%20have%26nbsp%3B%22Names%22%2C%20%22Dates%22%26nbsp%3B%26amp%3B%26nbsp%3B%22Types%22.%26nbsp%3B%20I%20would%20like%20to%20build%20a%20formula%20into%20spreadsheet%20%232%20that%20will%20let%20me%20know%20if%20the%20corresponding%20row%2Fentry%20appears%20in%20spreadsheet%20%231.%26nbsp%3B%20What%20functions%20are%20needed%20to%20make%20this%20work%3F%26nbsp%3B%20I%20would%20like%20for%26nbsp%3Bthe%20formula%26nbsp%3Bto%20match%20the%20exact%20%22Name%22%2C%20%22Date%22%20%26amp%3B%20%22Type%22%20in%20spreadsheet%20%231%20and%20to%20spit%20out%20a%20%22Yes%22%20(if%20it's%20found%20on%20spreadsheet%20%231)%20or%20%22No%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20multiple%20arguments%20(needing%20to%26nbsp%3Breference%20the%26nbsp%3Bname%2C%20date%20%26amp%3B%20time%20exactly)%26nbsp%3Bis%20what%20I%20believe%20is%20giving%20me%20the%20most%20trouble%20in%20figuring%20this%20out.%26nbsp%3B%20Any%20help%20would%20be%20very%20much%20appreciated!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-698119%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20Online%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-698193%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20Help%20-%20Cross%20Referencing%20Two%20Spreadsheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-698193%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F361726%22%20target%3D%22_blank%22%3E%40bepsky%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20such%20sample%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20259px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F119180iFC63B5EFEB55E5C5%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eit%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DIF(ISNUMBER(MATCH(1%2CINDEX((Sheet1!%24A%3A%24A%3D%24A2)*(Sheet1!%24B%3A%24B%3D%24B2)*(Sheet1!%24C%3A%24C%3D%24C2)%2C0)%2C0))%2C%22Yes%22%2C%22No%22)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-705334%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20Help%20-%20Cross%20Referencing%20Two%20Spreadsheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-705334%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F361726%22%20target%3D%22_blank%22%3E%40bepsky%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%203D%20referencing.%20Hope%20that%20helps.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-705556%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20Help%20-%20Cross%20Referencing%20Two%20Spreadsheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-705556%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F361726%22%20target%3D%22_blank%22%3E%40bepsky%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20attached%20file%2C%20the%20formula%20in%20Sheet2!D2%20is%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIF(COUNTIFS(Sheet1!A%3AA%2CA2%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ESheet1!B%3AB%2CB2%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ESheet1!C%3AC%2CC2)%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E%22Yes%22%2C%22No%22)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EThe%20foregoing%20formula%20counts%20the%20instances%20when%20the%203%20conditions%20are%20met%20and%20returns%20%22Yes%22%20if%20the%20count%20is%201%20or%20more%3B%20otherwise%2C%20it%20returns%20%22No%22.%3C%2FP%3E%3C%2FLINGO-BODY%3E
bepsky
Occasional Visitor

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

Related Conversations
Extentions Synchronization
Deleted in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies