Formula Help Needed (With an over abundance of examples)

[svcellular]

This is a table from a sheet called "Activations Demo" - There are many tables with similar information in this sheet
I'm aware I may have put in more than enough columns, but my brain is broken.

12/1/2015	1989	Cow
12/1/2015	1995	Eagle
12/1/2015	1127	Marko
12/2/2015	420	Polo
12/1/2015	1989	Blue
12/1/2015	1995	Black
12/1/2015	1127	Red
12/2/2015	420	Purple
12/2/2015	1104	Yellow
12/3/2015	1953	Orange
12/3/2015	7651	Indigo

<colgroup><col style="width: 120px"><col width="120"><col width="61"></colgroup><tbody>
</tbody>

This is a table labeled "Shipped" from a different sheet labeled "ESN List Demo"

10/23/2015	BM-LGLS751	Orange
10/23/2015	BM-LGLS751	Monkey
10/23/2015	BM-LGLS751	Dog
10/23/2015	BM-LGLS751	Red
10/23/2015	BM-MOT1526	Blue
10/23/2015	BM-MOT1526	Cow

<colgroup><col style="width: 93px"><col width="84"><col width="138"></colgroup><tbody>
</tbody>

This is what I'd like done in the new table labeled "Inventory" also in the "ESN List Demo" sheet

Insert A1 from "Shipped" Table if C1 from "Shipped" table is found anywhere in "Activations Demo" Sheet	B1 from "Shipped" Table if C1 from "Shipped" table is found anywhere in "Activations Demo" Sheet	C1 from "Shipped" Table only if the same value is found on any table in "Activations" Sheet
Insert A2 from "Shipped" Table if C2 from "Shipped" table is found anywhere in "Activations Demo" Sheet	B2 from "Shipped" Table if C2 from "Shipped" table is found anywhere in "Activations Demo" Sheet	C2 from "Shipped" Table only if the same value is found on any table in "Activations" Sheet
Insert A3 from "Shipped" Table if C3 from "Shipped" table is found anywhere in "Activations Demo" Sheet	B3 from "Shipped" Table if C3 from "Shipped" table is found anywhere in "Activations Demo" Sheet	C3 from "Shipped" Table only if the same value is found on any table in "Activations" Sheet

<colgroup><col style="width: 120px"><col width="120"><col width="120"></colgroup><tbody>
</tbody>

This is what "Inventory" would look like with the correct formula

10/23/2015	BM-LGLS751	Orange
10/23/2015	BM-LGLS751	Red
10/23/2015	BM-MOT1526	Blue
10/23/2015	BM-MOT1526	Cow

<colgroup><col style="width: 120px"><col width="120"><col width="120"></colgroup><tbody>
</tbody>

I know there will be blank rows in the above table as well, but I think I've overdone this post as it is.

Might have been an easier way to go about explaining this and giving examples, but I'm new to this stuff, so sorry if I over or under explained anything.

 

[Smitty]

It sounds like INDEX/MATCH will do what you want:

Book1
	A	B	C	D	E	F
1	Shipped Date	Order #	Product	Activated	Quantity	Product2
2	10/23/15	BM-MOT1526	Cow	12/01/15	1989	Cow
3				12/01/15	1995	Eagle
4				12/01/15	1127	Marko
5				12/02/15	420	Polo
6	10/23/15	BM-MOT1526	Blue	12/01/15	1989	Blue
7				12/01/15	1995	Black
8	10/23/15	BM-LGLS751	Red	12/01/15	1127	Red
9				12/02/15	420	Purple
10				12/02/15	1104	Yellow
11	10/23/15	BM-LGLS751	Orange	12/03/15	1953	Orange
12				12/03/15	7651	Indigo
Activations Demo

Cell Formulas
Range	Formula
A2	=IFERROR(INDEX(Shipped[Order Date],MATCH([@Product2],Shipped[Product],0),0),"")
A3	=IFERROR(INDEX(Shipped[Order Date],MATCH([@Product2],Shipped[Product],0),0),"")
A4	=IFERROR(INDEX(Shipped[Order Date],MATCH([@Product2],Shipped[Product],0),0),"")
A5	=IFERROR(INDEX(Shipped[Order Date],MATCH([@Product2],Shipped[Product],0),0),"")
A6	=IFERROR(INDEX(Shipped[Order Date],MATCH([@Product2],Shipped[Product],0),0),"")
A7	=IFERROR(INDEX(Shipped[Order Date],MATCH([@Product2],Shipped[Product],0),0),"")
A8	=IFERROR(INDEX(Shipped[Order Date],MATCH([@Product2],Shipped[Product],0),0),"")
A9	=IFERROR(INDEX(Shipped[Order Date],MATCH([@Product2],Shipped[Product],0),0),"")
A10	=IFERROR(INDEX(Shipped[Order Date],MATCH([@Product2],Shipped[Product],0),0),"")
A11	=IFERROR(INDEX(Shipped[Order Date],MATCH([@Product2],Shipped[Product],0),0),"")
A12	=IFERROR(INDEX(Shipped[Order Date],MATCH([@Product2],Shipped[Product],0),0),"")
B2	=IFERROR(INDEX(Shipped[Order '# ],MATCH([@Product2],Shipped[Product],0),0),"")
B3	=IFERROR(INDEX(Shipped[Order '# ],MATCH([@Product2],Shipped[Product],0),0),"")
B4	=IFERROR(INDEX(Shipped[Order '# ],MATCH([@Product2],Shipped[Product],0),0),"")
B5	=IFERROR(INDEX(Shipped[Order '# ],MATCH([@Product2],Shipped[Product],0),0),"")
B6	=IFERROR(INDEX(Shipped[Order '# ],MATCH([@Product2],Shipped[Product],0),0),"")
B7	=IFERROR(INDEX(Shipped[Order '# ],MATCH([@Product2],Shipped[Product],0),0),"")
B8	=IFERROR(INDEX(Shipped[Order '# ],MATCH([@Product2],Shipped[Product],0),0),"")
B9	=IFERROR(INDEX(Shipped[Order '# ],MATCH([@Product2],Shipped[Product],0),0),"")
B10	=IFERROR(INDEX(Shipped[Order '# ],MATCH([@Product2],Shipped[Product],0),0),"")
B11	=IFERROR(INDEX(Shipped[Order '# ],MATCH([@Product2],Shipped[Product],0),0),"")
B12	=IFERROR(INDEX(Shipped[Order '# ],MATCH([@Product2],Shipped[Product],0),0),"")
C2	=IFERROR(INDEX(Shipped[Product],MATCH([@Product2],Shipped[Product],0),0),"")
C3	=IFERROR(INDEX(Shipped[Product],MATCH([@Product2],Shipped[Product],0),0),"")
C4	=IFERROR(INDEX(Shipped[Product],MATCH([@Product2],Shipped[Product],0),0),"")
C5	=IFERROR(INDEX(Shipped[Product],MATCH([@Product2],Shipped[Product],0),0),"")
C6	=IFERROR(INDEX(Shipped[Product],MATCH([@Product2],Shipped[Product],0),0),"")
C7	=IFERROR(INDEX(Shipped[Product],MATCH([@Product2],Shipped[Product],0),0),"")
C8	=IFERROR(INDEX(Shipped[Product],MATCH([@Product2],Shipped[Product],0),0),"")
C9	=IFERROR(INDEX(Shipped[Product],MATCH([@Product2],Shipped[Product],0),0),"")
C10	=IFERROR(INDEX(Shipped[Product],MATCH([@Product2],Shipped[Product],0),0),"")
C11	=IFERROR(INDEX(Shipped[Product],MATCH([@Product2],Shipped[Product],0),0),"")
C12	=IFERROR(INDEX(Shipped[Product],MATCH([@Product2],Shipped[Product],0),0),"")

Note that you said you're set up with tables, so the example uses tables.

If you want to PM me your e-mail address I'll send you the example.

HTH,

 

[svcellular]

Almost, but I want to not show anything in the row if the product doesn't match.

I'm going to further over explain even though I know there's probably a much simpler way.

"Shipped" is an ongoing list of phones that have been shipped to me. The product in these rows is the ESN of the phone in that row.
The "Activations" sheet is where salespeople list phones going out, and one of the cells in each row is the ESN of said phone.
"Inventory" which is a table in the same sheet as "Shipped" is where I'd like to filter out phones that have been sold (on multiple tables in the activations sheet) from the ones on the "Shipped" sheet.

Then if the ESN comes over, I'd like for date and product code (which is in the B column) to come over as well so that I can see what phones should still be in inventory.

 

[FDibbins]

just FYI, x-posted here...
Search one sheet for matching info to fill cells in another sheet