Help! Referencing a cell based on ranged criteria.

[missdeannamarie]

Hello!

I have been stuck on this formula for quite some time and was hoping I could get some help from you genius excel people! Below is an example of my spreadsheet. What I am trying to achieve is a formula in Cell R2, that matches the value of cell B23 with cell range C1:Q1 (B23 has a drop down that chooses C1:Q1 values, so I would need R2 to change based on what is selected) and provides the value for the row R2 is in.

I hope that makes sense! I apologize ahead of time if this is super easy or has been on another thread, i have checked as much as i can.

Any help is much appreciated! Thank you!!!

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Products		OCT-15	NOV-15	DEC-15	JAN-16	FEB-16	MAR-16	APR-16	MAY-16	JUN-16	JUL-16	AUG-16	SEP-16	OCT-16	NOV-16	DEC-16	RESULT
2	SCM1		25	174	165	148	95	25	42	69	156	192	129	200	20	200	200
3	SCM2		99	93	89	45	48	58	89	126	18	85	10	200	20	200	200
4	SCM3		62	87	94	96	94	44	93	55	63	49	154	200	20	200	200
5	SCM4		50	200	133	85	166	10	142	15	76	69	72	200	20	200	200
6	SCM5		50	200	133	85	166	10	142	15	76	69	72	200	20	200	200
7	SCM6		50	200	133	85	166	10	142	15	76	69	72	200	20	200	200
8	QA1		50	200	133	85	166	10	142	15	76	69	72	200	20	200	200
9	QA2		50	200	133	85	166	10	142	15	76	69	72	200	20	200	200
10	QA3		50	500	133	85	166	10	142	15	76	69	72	200	20	200	200
11	QA4		50	200	133	100	166	10	142	15	76	69	72	200	20	200	200
12	QA5		50	200	133	85	166	10	142	15	76	69	72	200	20	200	200
13	QA6		50	200	133	85	166	10	142	15	76	69	72	200	20	200	200
14	FAC1		50	200	133	85	166	10	142	15	76	69	72	200	20	200	200
15	FAC2		50	200	133	85	166	10	142	15	76	69	72	200	20	200	200
16	FAC3		50	200	133	85	166	10	142	15	76	69	72	200	20	200	200
17	FAC4		50	200	133	85	166	10	142	15	76	69	72	200	20	200	200
18	FAC5		50	200	133	85	166	10	142	15	76	69	72	200	20	200	200
19	FAC6		50	200	133	85	166	10	142	15	76	69	72	200	20	200	200
20	OP1		50	200	133	85	166	10	142	15	76	69	72	200	20	200	200
21	Total Sales		986	3854	2476	1664	2893	287	2496	490	1453	1430	1445	3800	380	3800	3800	0
22
23	Select	OCT-15

<tbody>
</tbody>

 

[sheetspread]

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Products		15-Oct	15-Nov	15-Dec	16-Jan	16-Feb	16-Mar	16-Apr	16-May	16-Jun	16-Jul	16-Aug	16-Sep	16-Oct	16-Nov	16-Dec	RESULT
2	SCM1		25	174	165	148	95	25	42	69	156	192	129	200	20	200	200	25
3	SCM2		99	93	89	45	48	58	89	126	18	85	10	200	20	200	200	99
4	SCM3		62	87	94	96	94	44	93	55	63	49	154	200	20	200	200	62
5	SCM4		50	200	133	85	166	10	142	15	76	69	72	200	20	200	200	50
6	SCM5		50	200	133	85	166	10	142	15	76	69	72	200	20	200	200	50
7	SCM6		50	200	133	85	166	10	142	15	76	69	72	200	20	200	200	50
8	QA1		50	200	133	85	166	10	142	15	76	69	72	200	20	200	200	50
9	QA2		50	200	133	85	166	10	142	15	76	69	72	200	20	200	200	50
10	QA3		50	500	133	85	166	10	142	15	76	69	72	200	20	200	200	50
11	QA4		50	200	133	100	166	10	142	15	76	69	72	200	20	200	200	50
12	QA5		50	200	133	85	166	10	142	15	76	69	72	200	20	200	200	50
13	QA6		50	200	133	85	166	10	142	15	76	69	72	200	20	200	200	50
14	FAC1		50	200	133	85	166	10	142	15	76	69	72	200	20	200	200	50
15	FAC2		50	200	133	85	166	10	142	15	76	69	72	200	20	200	200	50
16	FAC3		50	200	133	85	166	10	142	15	76	69	72	200	20	200	200	50
17	FAC4		50	200	133	85	166	10	142	15	76	69	72	200	20	200	200	50
18	FAC5		50	200	133	85	166	10	142	15	76	69	72	200	20	200	200	50
19	FAC6		50	200	133	85	166	10	142	15	76	69	72	200	20	200	200	50
20	OP1		50	200	133	85	166	10	142	15	76	69	72	200	20	200	200	50
21	Total Sales		986	3854	2476	1664	2893	287	2496	490	1453	1430	1445	3800	380	3800	3800	986
22
23	Select	15-Oct
Sheet7

Cell Formulas
Range	Formula
R2	=HLOOKUP($B$23,$C$1:$Q$21,ROW(A2),0)

 

[missdeannamarie]

You are the absolute best!!! Thank you so much!!!