Dynamic Lookup Value using Column and Row

[rubz71]

Hi All,

Need a bit of help with a dynamic look-up value.
Sheet 1 contains the Machine Name and Day of the week.

Sheet 2 contains
list of Machines and their capacity per day.

I need to fill the "Capacity" column in sheet 1 based on the machine name and day of the week from Sheet 2.

Appreciate your help on this.

Sheet 1
Machine_Name	Day_of_the_Week	Machine_Name_DOW_Key	Capacity
ABC	Monday	ABC_Monday	1,200
ABC	Tuesday	ABC_Tuesday	1,300
ABC	Wednesday	ABC_Wednesday	1,350
ABC	Thursday	ABC_Thursday	1,250
ABC	Friday	ABC_Friday	1,100
ABC	Saturday	ABC_Saturday	1,000
ABC	Sunday	ABC_Sunday	900
XYZ	Monday	XYZ_Monday	1,400
XYZ	Tuesday	XYZ_Tuesday	1,350
XYZ	Wednesday	XYZ_Wednesday	1,300
XYZ	Thursday	XYZ_Thursday	1,200
XYZ	Friday	XYZ_Friday	1,250
XYZ	Saturday	XYZ_Saturday	1,100
XYZ	Sunday	XYZ_Sunday	1,000
PQR	Monday	PQR_Monday	1,800
PQR	Tuesday	PQR_Tuesday	1,700
PQR	Wednesday	PQR_Wednesday	1,600
PQR	Thursday	PQR_Thursday	1,500
PQR	Friday	PQR_Friday	1,400
PQR	Saturday	PQR_Saturday	1,300
PQR	Sunday	PQR_Sunday	1,200
CDE	Monday	CDE_Monday	700
CDE	Tuesday	CDE_Tuesday	600
CDE	Wednesday	CDE_Wednesday	500
CDE	Thursday	CDE_Thursday	400
CDE	Friday	CDE_Friday	300
CDE	Saturday	CDE_Saturday	200
CDE	Sunday	CDE_Sunday	250

Sheet 2
Machine	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
ABC	1,200	1,300	1,350	1,250	1,100	1,000	900
XYZ	1,400	1,350	1,300	1,200	1,250	1,100	1,000
PQR	1,800	1,700	1,600	1,500	1,400	1,300	1,200
CDE	700	600	500	400	300	200	250
IJK	1,900	1,600	1,350	2,250	1,100	1,000	900
LMN	3,400	3,350	3,300	3,200	3,250	3,100	3,000
EFG	5,800	5,700	5,600	5,500	5,400	5,300	5,200

 

[ouvay]

as per your screenshot... and the formula you shared in our conversation..

=INDEX(H3:M8,MATCH(A2,$G$2:$G$8,1),MATCH(B2,$H$2:$M$2,1))

should work

 

[kevin9999]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Machine_Name	Day_of_the_Week	Machine_Name_DOW_Key	Capacity
2	ABC	Monday	ABC_Monday	1200			Machine	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
3	ABC	Tuesday	ABC_Tuesday	1300			ABC	1,200	1,300	1,350	1,250	1,100	1,000	900
4	ABC	Wednesday	ABC_Wednesday	1350			XYZ	1,400	1,350	1,300	1,200	1,250	1,100	1,000
5	ABC	Thursday	ABC_Thursday	1250			PQR	1,800	1,700	1,600	1,500	1,400	1,300	1,200
6	ABC	Friday	ABC_Friday	1100			CDE	700	600	500	400	300	200	250
7	ABC	Saturday	ABC_Saturday	1000			IJK	1,900	1,600	1,350	2,250	1,100	1,000	900
8	ABC	Sunday	ABC_Sunday	900			LMN	3,400	3,350	3,300	3,200	3,250	3,100	3,000
9	XYZ	Monday	XYZ_Monday	1400			EFG	5,800	5,700	5,600	5,500	5,400	5,300	5,200
10	XYZ	Tuesday	XYZ_Tuesday	1350
11	XYZ	Wednesday	XYZ_Wednesday	1300
12	XYZ	Thursday	XYZ_Thursday	1200
13	XYZ	Friday	XYZ_Friday	1250
14	XYZ	Saturday	XYZ_Saturday	1100
15	XYZ	Sunday	XYZ_Sunday	1000
16	PQR	Monday	PQR_Monday	1800
17	PQR	Tuesday	PQR_Tuesday	1700
18	PQR	Wednesday	PQR_Wednesday	1600
19	PQR	Thursday	PQR_Thursday	1500
20	PQR	Friday	PQR_Friday	1400
21	PQR	Saturday	PQR_Saturday	1300
22	PQR	Sunday	PQR_Sunday	1200
23	CDE	Monday	CDE_Monday	700
24	CDE	Tuesday	CDE_Tuesday	600
25	CDE	Wednesday	CDE_Wednesday	500
26	CDE	Thursday	CDE_Thursday	400
27	CDE	Friday	CDE_Friday	300
28	CDE	Saturday	CDE_Saturday	200
29	CDE	Sunday	CDE_Sunday	250
Sheet1

Cell Formulas
Range	Formula
D2:D29	D2	=SUMPRODUCT(($G$3:$G$9=A2)*($H$2:$N$2=B2),$H$3:$N$9)

 

[rubz71]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Machine_Name	Day_of_the_Week	Machine_Name_DOW_Key	Capacity
2	ABC	Monday	ABC_Monday	1200			Machine	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
3	ABC	Tuesday	ABC_Tuesday	1300			ABC	1,200	1,300	1,350	1,250	1,100	1,000	900
4	ABC	Wednesday	ABC_Wednesday	1350			XYZ	1,400	1,350	1,300	1,200	1,250	1,100	1,000
5	ABC	Thursday	ABC_Thursday	1250			PQR	1,800	1,700	1,600	1,500	1,400	1,300	1,200
6	ABC	Friday	ABC_Friday	1100			CDE	700	600	500	400	300	200	250
7	ABC	Saturday	ABC_Saturday	1000			IJK	1,900	1,600	1,350	2,250	1,100	1,000	900
8	ABC	Sunday	ABC_Sunday	900			LMN	3,400	3,350	3,300	3,200	3,250	3,100	3,000
9	XYZ	Monday	XYZ_Monday	1400			EFG	5,800	5,700	5,600	5,500	5,400	5,300	5,200
10	XYZ	Tuesday	XYZ_Tuesday	1350
11	XYZ	Wednesday	XYZ_Wednesday	1300
12	XYZ	Thursday	XYZ_Thursday	1200
13	XYZ	Friday	XYZ_Friday	1250
14	XYZ	Saturday	XYZ_Saturday	1100
15	XYZ	Sunday	XYZ_Sunday	1000
16	PQR	Monday	PQR_Monday	1800
17	PQR	Tuesday	PQR_Tuesday	1700
18	PQR	Wednesday	PQR_Wednesday	1600
19	PQR	Thursday	PQR_Thursday	1500
20	PQR	Friday	PQR_Friday	1400
21	PQR	Saturday	PQR_Saturday	1300
22	PQR	Sunday	PQR_Sunday	1200
23	CDE	Monday	CDE_Monday	700
24	CDE	Tuesday	CDE_Tuesday	600
25	CDE	Wednesday	CDE_Wednesday	500
26	CDE	Thursday	CDE_Thursday	400
27	CDE	Friday	CDE_Friday	300
28	CDE	Saturday	CDE_Saturday	200
29	CDE	Sunday	CDE_Sunday	250
Sheet1

Cell Formulas
Range	Formula
D2:D29	D2	=SUMPRODUCT(($G$3:$G$9=A2)*($H$2:$N$2=B2),$H$3:$N$9)

oh wow, this works like a magic

Thanks for your help!!

 

[kevin9999]

oh wow, this works like a magic

Thanks for your help!!

You're welcome, and thanks for the feedback