Data needed based on Drop down

[sksanjeev786]

Hi Team,

Can i get the Statements from G,LQ (F,K,N Slide number) if i do the DROP Down in A3 column and i should get the data in Column C
Example like if change the Drop down to 4 in "A3" cell then it should pull data from cell O3 to O20 statements and another example if i change Dropdown to "18 "in Cell A3 , then i need L2 to L20 statements in Column C (highlighted)

book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2	Drop Down		Formula need			Slide number					Slide number			Slide number
3	3		Unaided Brand Awareness (Any Mention) - Bank of America			2	Unaided Brand Awareness (Any Mention) - Bank of America				3	Unaided Brand Awareness (Any Mention) - US Bank		4	Unaided Brand Awareness (Any Mention) - Wells Fargo
4			Brand Familiarity - Bank of America			6	Brand Familiarity - Bank of America				7	Brand Familiarity - US Bank		8	Brand Familiarity - Wells Fargo
5			Total Ad Awareness - Bank of America			9	Total Ad Awareness - Bank of America				18	Total Ad Awareness - US Bank		28	Total Ad Awareness - Wells Fargo
6			Digital Ad Awareness - Bank of America			10	Digital Ad Awareness - Bank of America				19	Digital Ad Awareness - US Bank		29	Digital Ad Awareness - Wells Fargo
7			TV Ad Awareness - Bank of America			11	TV Ad Awareness - Bank of America				20	TV Ad Awareness - US Bank		30	TV Ad Awareness - Wells Fargo
8			Aided Sponsorship Awareness - Bank of America			12	Aided Sponsorship Awareness - Bank of America				21	Aided Sponsorship Awareness - US Bank		31	Aided Sponsorship Awareness - Wells Fargo
9			Brand Favorability - Bank of America			13	Brand Favorability - Bank of America				22	Brand Favorability - US Bank		32	Brand Favorability - Wells Fargo
10			Brand Preference - Bank of America			14	Brand Preference - Bank of America				23	Brand Preference - US Bank		33	Brand Preference - Wells Fargo
11			Usage Intent - Bank of America			15	Usage Intent - Bank of America				24	Usage Intent - US Bank		34	Usage Intent - Wells Fargo
12						16					25			35
13			I am more favorable towards Bank of America because of their association with The Olympics			17	I am more favorable towards Bank of America because of their association with The Olympics				27	I am more favorable towards US Bank because of their association with The Olympics		36	I am more favorable towards Wells Fargo because of their association with The Olympics
14			I am more likely to consider Bank of America because of their association with The Olympics				I am more likely to consider Bank of America because of their association with The Olympics					I am more likely to consider US Bank because of their association with The Olympics			I am more likely to consider Wells Fargo because of their association with The Olympics
15			I am more likely to use Bank of America to make a purchase because of their association with The Olympics				I am more likely to use Bank of America to make a purchase because of their association with The Olympics					I am more likely to use US Bank to make a purchase because of their association with The Olympics			I am more likely to use Wells Fargo to make a purchase because of their association with The Olympics
16			I am more likely to recommend Bank of America because of their association with The Olympics				I am more likely to recommend Bank of America because of their association with The Olympics					I am more likely to recommend US Bank because of their association with The Olympics			I am more likely to recommend Wells Fargo because of their association with The Olympics
17			Bank of America is bringing people together because of their association with The Olympics				Bank of America is bringing people together because of their association with The Olympics					US Bank is bringing people together because of their association with The Olympics			Wells Fargo is bringing people together because of their association with The Olympics
18			Bank of America is inspiring because of their association with the Olympics				Bank of America is inspiring because of their association with the Olympics					US Bank is inspiring because of their association with the Olympics			Wells Fargo is inspiring because of their association with the Olympics
19			Bank of America's connection to The Olympics has a positive impact on society				Bank of America's connection to The Olympics has a positive impact on society					US Bank's connection to The Olympics has a positive impact on society			Wells Fargo's connection to The Olympics has a positive impact on society
20			Bank of America's connection to The Olympics makes the brand more relevant to me				Bank of America's connection to The Olympics makes the brand more relevant to me					US Bank's connection to The Olympics makes the brand more relevant to me			Wells Fargo's connection to The Olympics makes the brand more relevant to me
21
22
23						2
24						3
25						4
26						5
27						6
28						7
29						8
30						9
31						10
32						11
33						12
34						13
35						14
36						15
37						16
38						17
39						18
40						19
41						20
42						21
43						22
44						23
45						24
46						25
47						26
48						27
49						28
50						29
51						30
52						31
53						32
Sheet1

Cell Formulas
Range	Formula
F24:F53	F24	=F23+1

Cells with Data Validation
Cell	Allow	Criteria
A3	List	=$F$23:$F$53

 

[StephenCrump]

Try: =LET(d,F3:O20,c,COLUMN(d),s,SUM((A3=d)*(c-MIN(c)+2)),i,IF(s=0,"n/a",INDEX(d,,s)),IF(i=0,"",i))

 

[sksanjeev786]

Try: =LET(d,F3:O20,c,COLUMN(d),s,SUM((A3=d)*(c-MIN(c)+2)),i,IF(s=0,"n/a",INDEX(d,,s)),IF(i=0,"",i))

Perfect working very well

Have a curious about this could you please simply this formula I just want to understand how does it work

I know it will take time to explain but please consider it as optional

 

[StephenCrump]

No problem. Here it is, broken down into steps:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2	Drop Down					Slide No					Slide No			Slide No
3	18		GG			2	AAA				3	GG		4	PP
4			HH			6	BBB				7	HH		8	QQ
5			II			9	CCC				18	II		28	RR
6			JJ			10	DDD				19	JJ		29	SS
7
8			KK				EEE					KK			TT
9
10			Step 1	Column numbers
11							2	3	4	5	6	7	8	9	10
12			Step 2	Find match
13							FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
14							FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
15							FALSE	FALSE	FALSE	FALSE	FALSE	TRUE	FALSE	FALSE	FALSE
16							FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
17							FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
18							FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
19			Step 3	Get match column number
20							7
21			Step 4	Get column
22							GG
23							HH
24							II
25							JJ
26							0
27							KK
28			Step 5	Don't show 0's
29							GG
30							HH
31							II
32							JJ
33
34							KK
35
Sheet1

Cell Formulas
Range	Formula
C3:C8	C3	=LET(d,F3:N8,c,COLUMN(d),s,SUM((A3=d)*(c-MIN(c)+2)),i,IF(s=0,"n/a",INDEX(d,,s)),IF(i=0,"",i))
G11:O11	G11	=LET(d,F3:N8,c,COLUMN(d),c-MIN(c)+2)
G13:O18	G13	=LET(d,F3:N8,A3=d)
G20	G20	=SUM(G13#*G11#)
G22:G27	G22	=LET(d,F3:N8,INDEX(d,,G20))
G29:G34	G29	=LET(d,F3:N8,i,INDEX(d,,G20),IF(i=0,"",i))
Dynamic array formulas.

 

[sksanjeev786]

Waw!

No problem. Here it is, broken down into steps:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2	Drop Down					Slide No					Slide No			Slide No
3	18		GG			2	AAA				3	GG		4	PP
4			HH			6	BBB				7	HH		8	QQ
5			II			9	CCC				18	II		28	RR
6			JJ			10	DDD				19	JJ		29	SS
7
8			KK				EEE					KK			TT
9
10			Step 1	Column numbers
11							2	3	4	5	6	7	8	9	10
12			Step 2	Find match
13							FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
14							FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
15							FALSE	FALSE	FALSE	FALSE	FALSE	TRUE	FALSE	FALSE	FALSE
16							FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
17							FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
18							FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
19			Step 3	Get match column number
20							7
21			Step 4	Get column
22							GG
23							HH
24							II
25							JJ
26							0
27							KK
28			Step 5	Don't show 0's
29							GG
30							HH
31							II
32							JJ
33
34							KK
35
Sheet1

Cell Formulas
Range	Formula
C3:C8	C3	=LET(d,F3:N8,c,COLUMN(d),s,SUM((A3=d)*(c-MIN(c)+2)),i,IF(s=0,"n/a",INDEX(d,,s)),IF(i=0,"",i))
G11:O11	G11	=LET(d,F3:N8,c,COLUMN(d),c-MIN(c)+2)
G13:O18	G13	=LET(d,F3:N8,A3=d)
G20	G20	=SUM(G13#*G11#)
G22:G27	G22	=LET(d,F3:N8,INDEX(d,,G20))
G29:G34	G29	=LET(d,F3:N8,i,INDEX(d,,G20),IF(i=0,"",i))
Dynamic array formulas.

Waw!!! thank you..... so much for your time and help with this