SOLVED

Compare 2 tables

%3CLINGO-SUB%20id%3D%22lingo-sub-3087942%22%20slang%3D%22en-US%22%3ECompare%202%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3087942%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20list%20in%201%20sheet%20and%20another%20list%20in%20another%20sheet.%3C%2FP%3E%3CP%3EI%20need%20to%20tag%20the%20ones%20on%20sheet%202%20that%20are%20NOT%20IN%20the%20other%20list%20on%20the%20other%20sheet.%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20criteria%20is%20on%20Co%20Name%2C%20Invoice%20Number%2C%20and%20amount%20which%20makes%20it%20difficult%20for%20me.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20see%20attached%20file%20for%20a%20simple%20example.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethank%20you%20very%20much%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3087942%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3088505%22%20slang%3D%22en-US%22%3EBetreff%3A%20Compare%202%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3088505%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1029845%22%20target%3D%22_blank%22%3E%40Tony2021%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHier%20ein%20einfaches%20Beispiel%20wie%20gew%C3%BCnscht.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHoffe%2C%20dass%20ich%20Ihnen%20mit%20diesen%20Informationen%20und%20eingef%C3%BCgte%20Datei%20weiterhelfen%20konnte.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel-blog%2Fmeet-niko-chatzoudis-excel-forum-contributor%2Fba-p%2F2941385%22%20target%3D%22_blank%22%3ENikolinoDE%3C%2FA%3E%3C%2FP%3E%3CP%3EWar%20die%20Antwort%20n%C3%BCtzlich%3F%20Markiere%20sie%20als%20hilfreich!%3C%2FP%3E%3CP%3ESomit%20hilfst%20du%20allen%20Forenteilnehmern%20weiter.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3088515%22%20slang%3D%22en-US%22%3ERe%3A%20Compare%202%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3088515%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1029845%22%20target%3D%22_blank%22%3E%40Tony2021%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(ISNA(VLOOKUP(A2%26amp%3BB2%26amp%3BC2%2CPaid!%24A%242%3A%24A%248%26amp%3BPaid!%24B%242%3A%24B%248%26amp%3BPaid!%24C%242%3A%24C%248%2C1%2CFALSE))%2C%22Not%20Paid%22%2C%22Paid%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20formula%20works%20in%20my%20spreadsheet.%20Enter%20formula%20as%20arrayformula%20with%20ctrl%2Bshift%2Benter%20if%20you%20don't%20work%20with%20Office365%20or%202021.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3088640%22%20slang%3D%22en-US%22%3ERe%3A%20Compare%202%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3088640%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Quadruple_Pawn%2C%20thank%20you.%20It%20worked%20perfectly!%20thank%20you%20and%20very%20grateful%20for%20your%20response.%3CBR%20%2F%3E%3CBR%20%2F%3EFYI%20(and%20for%20other%20users%20that%20read)%20I%20work%20with%20Office%20365%20so%20I%20did%20not%20need%20ctrl%2Bshift%2Benter.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3A%3C%2FP%3E%3CP%3Ethat%20would%20work%20if%20the%20data%20were%20the%20same%20positions%20between%20both%20tables%20and%20although%20the%20example%20I%20posted%20was%20as%20such%2C%20this%20was%20only%20a%20simple%20example%20and%20not%20the%20case%20in%20areal%20world%20scenario.%26nbsp%3B%20thank%20you%20for%20the%20response%20though.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3088789%22%20slang%3D%22en-US%22%3ERe%3A%20Compare%202%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3088789%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1029845%22%20target%3D%22_blank%22%3E%40Tony2021%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DIF(%20SUM(%20MMULT(%20--(A2%3AC2%3DPaid!%24A%242%3A%24C%248)%2C%7B1%3B1%3B1%7D)%20)%20%3D%203%2C%20%22Paid%22%2C%20%22Not%20Paid%22%20)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Super Contributor

Hello,

 

I have a list in 1 sheet and another list in another sheet.

I need to tag the ones on sheet 2 that are NOT IN the other list on the other sheet. 

The criteria is on Co Name, Invoice Number, and amount which makes it difficult for me.  

 

Please see attached file for a simple example.

 

thank you very much

 

5 Replies

@Tony2021 

 

Hier ein einfaches Beispiel wie gewünscht.

 

Hoffe, dass ich Ihnen mit diesen Informationen und eingefügte Datei weiterhelfen konnte.

 

NikolinoDE

War die Antwort nützlich? Markiere sie als hilfreich!

Somit hilfst du allen Forenteilnehmern weiter.

 

best response confirmed by Tony2021 (Super Contributor)
Solution

@Tony2021 

=IF(ISNA(VLOOKUP(A2&B2&C2,Paid!$A$2:$A$8&Paid!$B$2:$B$8&Paid!$C$2:$C$8,1,FALSE)),"Not Paid","Paid")

 

This formula works in my spreadsheet. Enter formula as arrayformula with ctrl+shift+enter if you don't work with Office365 or 2021.

Hi Quadruple_Pawn, thank you. It worked perfectly! thank you and very grateful for your response.

FYI (and for other users that read) I work with Office 365 so I did not need ctrl+shift+enter.

 

Nikolino:

that would work if the data were the same positions between both tables and although the example I posted was as such, this was only a simple example and not the case in areal world scenario.  thank you for the response though. 

@Tony2021 

As variant

=IF( SUM( MMULT( --(A2:C2=Paid!$A$2:$C$8),{1;1;1}) ) = 3, "Paid", "Not Paid" )
Nice! thank you Sergei. Little cleaner and a far outside of my IQ.