Apr 02 2020 06:25 AM
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
ProductFileName | UPC | ProductSupplier | PackagingItem | PackagingItemSupplier | PackagingMaterial | MaterialClassification | ItemWeight |
Red/Black Plums x 6 | 998574 | The Fresh Produce Centre Limited | Stonefruit black pulp tray 6A | Cullen Packaging | Other Paper | Paper | 23 |
Red/Black Plums x 6 | 998574 | The Fresh Produce Centre Limited | Stonefruit 60x80mm product label | Paragon | Paper laminate labels | Labels | 2 |
Red/Black Plums x 6 | 998574 | The Fresh Produce Centre Limited | Plain Flow wrap | Paragon | Polyester | Flexible Films | 2 |
Red/Black Plums x 6 | 998574 | Greenyard Fresh UK Ltd | 460 P160 Film | ESP Packaging | Polypropylene | Plastic | 0 |
Tab Two
Product Code | Name | Supplier Name | Packaging Level | Base Material | Extended Material | Packaging Type | Total Packaging Weight (g) |
998574 | Red/Black Plums X 6 | Greenyard Fresh Uk Ltd | Primary | Paper | Paper | Tray | 17.18 |
998574 | Red/Black Plums X 6 | Greenyard Fresh Uk Ltd | Primary | Plastic | Plastic | Bag | 3.08 |
998574 | Red/Black Plums X 6 | Worldwide Fruit Limited | Primary | Paper | Paper | Tray | 17.18 |
998574 | Red/Black Plums X 6 | Worldwide Fruit Limited | Primary | Plastic | Plastic | Bag | 3.08 |
998574 | Red/Black Plums X 6 | Total Berry | Primary | Paper | Paper | Tray | 17.18 |
998574 | Red/Black Plums X 6 | Total Berry | Primary | Plastic | Plastic | Bag | 3.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.
Apr 03 2020 12:39 AM
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.
Apr 03 2020 01:10 AM
@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 :)
Apr 03 2020 12:39 AM
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.