Auto Populate Column in Table Based on Multiple Criteria in Another Table


New Member
May 31, 2018
I have a spreadsheet in which I have tables that contain data pulled from our inventory software.

The main table CI_Item contains information for everything we make or buy with the fist column being the item number.

All of the jars have a value of PM10 in the ProductLine column; those that have not been discontinued also have a value of R in the ProductType column.

I would like to populate the first column in a new table for jars only (JarTable) that would give me the item number of everything where ProductLine=PM10 and ProductType=R. My thought is that when a jar is added or discontinued in the inventory software and updated on the CI_Item table, that the JarTable would update also. Once I have the item numbers on the JarTable, I can pull data from other data tables using Index/Match.

After working it our for jars, I would then use same idea with different ProductLine values to create tables for caps, boxes, raw materials and finished goods.

I've been reading internet posts, asking co-workers and watching training videos. I've tried a few things, but being new to complicated formulas, I've not yet found the solution.

Any help would be gratefully appreciated.

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Watch MrExcel Video

Forum statistics

Latest member