SOLVED

Excel Formula Help

%3CLINGO-SUB%20id%3D%22lingo-sub-1275121%22%20slang%3D%22en-US%22%3EExcel%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1275121%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20two%20tabs.%20Each%20tab%20possible%20has%20the%20same%20information%20in%20a%20number%20of%20rows%20and%20columns.%20I%20am%20wanting%20to%20check%20if%20one%20row%20in%20tab%20one%20matches%20any%20row%20in%20tab%20two.%20For%20example%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETab%20One%3C%2FP%3E%3CTABLE%20width%3D%221470%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22301%22%3EProductFileName%3C%2FTD%3E%3CTD%20width%3D%2245%22%3EUPC%3C%2FTD%3E%3CTD%20width%3D%22215%22%3EProductSupplier%3C%2FTD%3E%3CTD%20width%3D%22220%22%3EPackagingItem%3C%2FTD%3E%3CTD%20width%3D%22209%22%3EPackagingItemSupplier%3C%2FTD%3E%3CTD%20width%3D%22175%22%3EPackagingMaterial%3C%2FTD%3E%3CTD%20width%3D%22205%22%3EMaterialClassification%3C%2FTD%3E%3CTD%20width%3D%22100%22%3EItemWeight%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ERed%2FBlack%20Plums%20x%206%3C%2FTD%3E%3CTD%3E998574%3C%2FTD%3E%3CTD%3EThe%20Fresh%20Produce%20Centre%20Limited%3C%2FTD%3E%3CTD%3EStonefruit%20black%20pulp%20tray%206A%3C%2FTD%3E%3CTD%3ECullen%20Packaging%3C%2FTD%3E%3CTD%3EOther%20Paper%3C%2FTD%3E%3CTD%3EPaper%3C%2FTD%3E%3CTD%3E23%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ERed%2FBlack%20Plums%20x%206%3C%2FTD%3E%3CTD%3E998574%3C%2FTD%3E%3CTD%3EThe%20Fresh%20Produce%20Centre%20Limited%3C%2FTD%3E%3CTD%3EStonefruit%2060x80mm%20product%20label%3C%2FTD%3E%3CTD%3EParagon%3C%2FTD%3E%3CTD%3EPaper%20laminate%20labels%3C%2FTD%3E%3CTD%3ELabels%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ERed%2FBlack%20Plums%20x%206%3C%2FTD%3E%3CTD%3E998574%3C%2FTD%3E%3CTD%3EThe%20Fresh%20Produce%20Centre%20Limited%3C%2FTD%3E%3CTD%3EPlain%20Flow%20wrap%3C%2FTD%3E%3CTD%3EParagon%3C%2FTD%3E%3CTD%3EPolyester%3C%2FTD%3E%3CTD%3EFlexible%20Films%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ERed%2FBlack%20Plums%20x%206%3C%2FTD%3E%3CTD%3E998574%3C%2FTD%3E%3CTD%3EGreenyard%20Fresh%20UK%20Ltd%3C%2FTD%3E%3CTD%3E460%20P160%20Film%3C%2FTD%3E%3CTD%3EESP%20Packaging%3C%2FTD%3E%3CTD%3EPolypropylene%3C%2FTD%3E%3CTD%3EPlastic%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETab%20Two%3C%2FP%3E%3CTABLE%20width%3D%222163px%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22111.5px%22%3EProduct%20Code%3C%2FTD%3E%3CTD%20width%3D%221059.5px%22%3EName%3C%2FTD%3E%3CTD%20width%3D%22263px%22%3ESupplier%20Name%3C%2FTD%3E%3CTD%20width%3D%22114px%22%3EPackaging%20Level%3C%2FTD%3E%3CTD%20width%3D%22100px%22%3EBase%20Material%3C%2FTD%3E%3CTD%20width%3D%22205px%22%3EExtended%20Material%3C%2FTD%3E%3CTD%20width%3D%22120px%22%3EPackaging%20Type%3C%2FTD%3E%3CTD%20width%3D%22189px%22%3ETotal%20Packaging%20Weight%20(g)%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22111.5px%22%3E998574%3C%2FTD%3E%3CTD%20width%3D%221059.5px%22%3ERed%2FBlack%20Plums%20X%206%3C%2FTD%3E%3CTD%20width%3D%22263px%22%3EGreenyard%20Fresh%20Uk%20Ltd%3C%2FTD%3E%3CTD%20width%3D%22114px%22%3EPrimary%3C%2FTD%3E%3CTD%20width%3D%22100px%22%3EPaper%3C%2FTD%3E%3CTD%20width%3D%22205px%22%3EPaper%3C%2FTD%3E%3CTD%20width%3D%22120px%22%3ETray%3C%2FTD%3E%3CTD%20width%3D%22189px%22%3E17.18%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22111.5px%22%3E998574%3C%2FTD%3E%3CTD%20width%3D%221059.5px%22%3ERed%2FBlack%20Plums%20X%206%3C%2FTD%3E%3CTD%20width%3D%22263px%22%3EGreenyard%20Fresh%20Uk%20Ltd%3C%2FTD%3E%3CTD%20width%3D%22114px%22%3EPrimary%3C%2FTD%3E%3CTD%20width%3D%22100px%22%3EPlastic%3C%2FTD%3E%3CTD%20width%3D%22205px%22%3EPlastic%3C%2FTD%3E%3CTD%20width%3D%22120px%22%3EBag%3C%2FTD%3E%3CTD%20width%3D%22189px%22%3E3.08%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22111.5px%22%3E998574%3C%2FTD%3E%3CTD%20width%3D%221059.5px%22%3ERed%2FBlack%20Plums%20X%206%3C%2FTD%3E%3CTD%20width%3D%22263px%22%3EWorldwide%20Fruit%20Limited%3C%2FTD%3E%3CTD%20width%3D%22114px%22%3EPrimary%3C%2FTD%3E%3CTD%20width%3D%22100px%22%3EPaper%3C%2FTD%3E%3CTD%20width%3D%22205px%22%3EPaper%3C%2FTD%3E%3CTD%20width%3D%22120px%22%3ETray%3C%2FTD%3E%3CTD%20width%3D%22189px%22%3E17.18%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22111.5px%22%3E998574%3C%2FTD%3E%3CTD%20width%3D%221059.5px%22%3ERed%2FBlack%20Plums%20X%206%3C%2FTD%3E%3CTD%20width%3D%22263px%22%3EWorldwide%20Fruit%20Limited%3C%2FTD%3E%3CTD%20width%3D%22114px%22%3EPrimary%3C%2FTD%3E%3CTD%20width%3D%22100px%22%3EPlastic%3C%2FTD%3E%3CTD%20width%3D%22205px%22%3EPlastic%3C%2FTD%3E%3CTD%20width%3D%22120px%22%3EBag%3C%2FTD%3E%3CTD%20width%3D%22189px%22%3E3.08%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22111.5px%22%3E998574%3C%2FTD%3E%3CTD%20width%3D%221059.5px%22%3ERed%2FBlack%20Plums%20X%206%3C%2FTD%3E%3CTD%20width%3D%22263px%22%3ETotal%20Berry%3C%2FTD%3E%3CTD%20width%3D%22114px%22%3EPrimary%3C%2FTD%3E%3CTD%20width%3D%22100px%22%3EPaper%3C%2FTD%3E%3CTD%20width%3D%22205px%22%3EPaper%3C%2FTD%3E%3CTD%20width%3D%22120px%22%3ETray%3C%2FTD%3E%3CTD%20width%3D%22189px%22%3E17.18%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22111.5px%22%3E998574%3C%2FTD%3E%3CTD%20width%3D%221059.5px%22%3ERed%2FBlack%20Plums%20X%206%3C%2FTD%3E%3CTD%20width%3D%22263px%22%3ETotal%20Berry%3C%2FTD%3E%3CTD%20width%3D%22114px%22%3EPrimary%3C%2FTD%3E%3CTD%20width%3D%22100px%22%3EPlastic%3C%2FTD%3E%3CTD%20width%3D%22205px%22%3EPlastic%3C%2FTD%3E%3CTD%20width%3D%22120px%22%3EBag%3C%2FTD%3E%3CTD%20width%3D%22189px%22%3E3.08%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIdeally%2C%20I%20would%20need%20a%20formula%20that%20will%20return%20the%20last%20value%20of%20tab%20two%2C%20column%26nbsp%3BTotal%20Packaging%20Weight%20(g).%20Sol%20long%20as%20a%20number%20of%20conditions%20are%20met%2C%20these%20are%20that%20the%20UPC%2C%20Name%2C%20Product%20Supplier%20etc%20match%20in%20both%20tabs.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1275121%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1277819%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1277819%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F606261%22%20target%3D%22_blank%22%3E%40michaelhilland%3C%2FA%3E%26nbsp%3BYou%20indicate%20that%20you%20are%20on%20Office365%2C%20so%20you%20might%20have%20access%20to%20the%20new%20FILTER%20function.%20I%20copied%20your%20tables%20and%20pasted%20them%20in%20a%20new%20sheet%20(all%20in%20one%20tab)%20with%20some%20color%20coding%20to%20visualise%20the%20relationships%20between%20the%20two%20tables.%20Please%20see%20attached.%20The%20results%20are%20in%20column%20I.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1277901%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1277901%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BThats%20amazing%2C%20thank%20you.%20It%20worked%20perfectly.%20I%20have%20never%20heard%20of%20the%20filter%20function!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHave%20a%20great%20day%20and%20keep%20safe%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1277942%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1277942%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F606261%22%20target%3D%22_blank%22%3E%40michaelhilland%3C%2FA%3E%26nbsp%3BYou're%20welcome!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi,

 

I have two tabs. Each tab possible has the same information in a number of rows and columns. I am wanting to check if one row in tab one matches any row in tab two. For example:

 

Tab One

ProductFileNameUPCProductSupplierPackagingItemPackagingItemSupplierPackagingMaterialMaterialClassificationItemWeight
Red/Black Plums x 6998574The Fresh Produce Centre LimitedStonefruit black pulp tray 6ACullen PackagingOther PaperPaper23
Red/Black Plums x 6998574The Fresh Produce Centre LimitedStonefruit 60x80mm product labelParagonPaper laminate labelsLabels2
Red/Black Plums x 6998574The Fresh Produce Centre LimitedPlain Flow wrapParagonPolyesterFlexible Films2
Red/Black Plums x 6998574Greenyard Fresh UK Ltd460 P160 FilmESP PackagingPolypropylenePlastic0

 

Tab Two

Product CodeNameSupplier NamePackaging LevelBase MaterialExtended MaterialPackaging TypeTotal Packaging Weight (g)
998574Red/Black Plums X 6Greenyard Fresh Uk LtdPrimaryPaperPaperTray17.18
998574Red/Black Plums X 6Greenyard Fresh Uk LtdPrimaryPlasticPlasticBag3.08
998574Red/Black Plums X 6Worldwide Fruit LimitedPrimaryPaperPaperTray17.18
998574Red/Black Plums X 6Worldwide Fruit LimitedPrimaryPlasticPlasticBag3.08
998574Red/Black Plums X 6Total BerryPrimaryPaperPaperTray17.18
998574Red/Black Plums X 6Total BerryPrimaryPlasticPlasticBag3.08

 

Ideally, I would need a formula that will return the last value of tab two, column Total Packaging Weight (g). Sol long as a number of conditions are met, these are that the UPC, Name, Product Supplier etc match in both tabs. 

3 Replies
Highlighted
Best Response confirmed by michaelhilland (New Contributor)
Solution

@michaelhilland You indicate that you are on Office365, so you might have access to the new FILTER function. I copied your tables and pasted them in a new sheet (all in one tab) with some color coding to visualise the relationships between the two tables. Please see attached. The results are in column I.

Highlighted

@Riny_van_Eekelen Thats amazing, thank you. It worked perfectly. I have never heard of the filter function! 

 

Have a great day and keep safe

Highlighted

@michaelhilland You're welcome!