Help with lookup

[mbee14]

Hi all, I'm pretty new with Excel, so I would really appreciate any help I can get with this. I have 2 workbooks, one of these is updated with new values every day with rows added onto the existing ones. It looks something like this, with rows going into thousands.

I need Excel to pull the values for SHIFT and PRODUCT from the above workbook and enter them into a new one, which looks something like this:

I'd also like to add that shift values can only be A, B, C or D, however for product I have about 200 different possible values. I hope all that made sense.

 

[kweaver]

Welcome.

What is it you're trying to find? How do you distinguish between "day" and "night" for the shift and product? Post an example of the solution(s) you're looking to generatel.

 

[mbee14]

My dayshift can only be A or C, nightshift can only be B or D. These shifts alternate every 4 days. My original sheet is not static, and will add rows every day, so I can't just reference it to certain cells. I don't need the fields saying if its day or night, those were more just as a reference. Here is a screenshot of what I would want it to look like. In this example I3=C2 and J3=D2. Basically what I need it to do, is update fields I3 and J3 automatically based off of date and machine number. Does that help? I'm horrible at explaining stuff, sorry.

 

[kweaver]

How about:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Date	Machine	Shift	Product			12/1/2019
2	12/1/2019	1	a	fit					Shift	Product
3	12/1/2019	1	b	nrse			1	day	a	fit
4	12/1/2019	2	a	lg			1	night	b	nrse
5	12/1/2019	2	b	msc
6	12/2/2019	3	c	trge			2	day	c
7	12/2/2019	3	d	md			2	night	b	msc
8	12/2/2019	4	c	uni
9	12/2/2019	4	d	uni
Sheet1

Cell Formulas
Range	Formula
J3:J4, J6:J7	J3	=IFERROR(INDIRECT("D"&SUMPRODUCT((A:A=$G$1)*(C:C=$I3)*(B:B=$G3)*ROW(A:A))),"")