I need help!

[kendry]

Hello everyone,

I am working on a dashboard to automatically calculate line efficiency based on product type and production line.

I have a total of 12 production lines and over 100 different product types. Each line has a set amount of product coming out (yield) depending on the product type. Example: line 1 yields 1000 units per hour of product X.

I have set up all the product types in a single column and all the production lines in multiple adjacent columns so that the product type and the production line intersect one another, and reflects the yield value on that intersection (a given cell).

My question is: how can I set a random cell (A1 for example) to display the yield value from an intersection? Provided that two other cell (A2 & A3 for example) are showing the production line and the product type (example: A2 = Line 1 & A3 = Product X... A1 then MUST equal 1000).

I thought VLOOKUP would do the trick, but I'm seriously stuck!

Please help me somebody! hahahahahahahahahahaha

View:

 

[Toadstool]

Hi Kendry,

I'm not sure I'm following you correctly but maybe this example will point you in the right direction.

Kendry.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Line	Product	Yield	Line 1	Line 2	Line 3	Line 4	Line 5	Line 6	Line 7	Line 8
2	1	Wagon Wheel	12	12	170	60	21	160	378	440	165
3	2	Snickers	70	88	70	162	217	328	162	420	220
4	3	Mars Bar	114	92	70	114	105	328	126	400	440
5	4	Milky Way	224	116	195	180	224	200	81	320	297
6	5	Skittles	344	92	120	240	182	344	162	30	308
7	6	Banjo Bar	90	52	210	156	77	200	90	280	176
Sheet1

Cell Formulas
Range	Formula
C2:C7	C2	=INDEX($D$2:$K$7,ROW()-ROW($C$1),MATCH("Line "&A2,$D$1:$K$1,0))

 

[kendry]

Hi Kendry,

I'm not sure I'm following you correctly but maybe this example will point you in the right direction.

Kendry.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Line	Product	Yield	Line 1	Line 2	Line 3	Line 4	Line 5	Line 6	Line 7	Line 8
2	1	Wagon Wheel	12	12	170	60	21	160	378	440	165
3	2	Snickers	70	88	70	162	217	328	162	420	220
4	3	Mars Bar	114	92	70	114	105	328	126	400	440
5	4	Milky Way	224	116	195	180	224	200	81	320	297
6	5	Skittles	344	92	120	240	182	344	162	30	308
7	6	Banjo Bar	90	52	210	156	77	200	90	280	176
Sheet1

Cell Formulas
Range	Formula
C2:C7	C2	=INDEX($D$2:$K$7,ROW()-ROW($C$1),MATCH("Line "&A2,$D$1:$K$1,0))

Thank you very much @Toadstool! It did point me in right direction, I solved it.