Index Match to Variable Sheets based on cell value

[jamesmev]

Hello Excel Masters -
This one has me scratching my balding head.

I have a fun one and I don't know where to start.

on Sheet1 - A1 - - There is a number which is represented as a different sheet.

1 = Sheet3
2 = Sheet4
3 = Sheet5
4 = Sheet6.


If sheet 1 A1=1
then I need sheet2 to provide all the results that correspond to what is on
Sheet2 = A1 within Sheet 3.

Sheet2 A1 = Yellow

Sheet 3 =
A B
Yellow Tennis
Brown Dog
Gold Watch
Orange Flag
Yellow Sun
Yellow banana

The results then are displayed on
Sheet 2 =A2 xlDown
Tennis
Sun
Banana

Then loop across rows until empty rows.


I'm hoping for a miracle.

 

[Snakehips]

@jamesmev Is this along the right lines?

Book2
	A	B
1	Yellow	Tennis
2	Brown	Dog
3	Gold	Watch
4	Orange	Flag
5	Yellow	Sun
6	Yellow	Banana
7
My Sheet 3

Book2
	A	B
1	Gold	Bullion
2	Brown	Stuff
3	Yellow	Submarine
4	Orange	State
5	Yellow	Ocre
6	Yellow	Jaundice
7	Black	Magic
8	Yellow	Peril
9
Sheet4

Book2
	A
1	1
Sheet1

Book2
	A
1	yellow
2	Tennis
3	Sun
4	Banana
5
Sheet2

Cell Formulas
Range	Formula
A2:A4	A2	=LET(Sht,"'"&CHOOSE(Sheet1!A1,"My Sheet 3","Sheet4","Sheet5","Sheet6")&"'!",FILTER(INDIRECT(Sht&"B:B"),INDIRECT(Sht&"A:A")=A1,"No Match"))
Dynamic array formulas.

Book2
	A
1	2
Sheet1

Book2
	A
1	yellow
2	Submarine
3	Ocre
4	Jaundice
5	Peril
6
Sheet2

Cell Formulas
Range	Formula
A2:A5	A2	=LET(Sht,"'"&CHOOSE(Sheet1!A1,"My Sheet 3","Sheet4","Sheet5","Sheet6")&"'!",FILTER(INDIRECT(Sht&"B:B"),INDIRECT(Sht&"A:A")=A1,"No Match"))
Dynamic array formulas.

 

[jamesmev]

@jamesmev Is this along the right lines?

Book2
	A	B
1	Yellow	Tennis
2	Brown	Dog
3	Gold	Watch
4	Orange	Flag
5	Yellow	Sun
6	Yellow	Banana
7
My Sheet 3

Book2
	A	B
1	Gold	Bullion
2	Brown	Stuff
3	Yellow	Submarine
4	Orange	State
5	Yellow	Ocre
6	Yellow	Jaundice
7	Black	Magic
8	Yellow	Peril
9
Sheet4

Book2
	A
1	1
Sheet1

Book2
	A
1	yellow
2	Tennis
3	Sun
4	Banana
5
Sheet2

Cell Formulas
Range	Formula
A2:A4	A2	=LET(Sht,"'"&CHOOSE(Sheet1!A1,"My Sheet 3","Sheet4","Sheet5","Sheet6")&"'!",FILTER(INDIRECT(Sht&"B:B"),INDIRECT(Sht&"A:A")=A1,"No Match"))
Dynamic array formulas.

Book2
	A
1	2
Sheet1

Book2
	A
1	yellow
2	Submarine
3	Ocre
4	Jaundice
5	Peril
6
Sheet2

Cell Formulas
Range	Formula
A2:A5	A2	=LET(Sht,"'"&CHOOSE(Sheet1!A1,"My Sheet 3","Sheet4","Sheet5","Sheet6")&"'!",FILTER(INDIRECT(Sht&"B:B"),INDIRECT(Sht&"A:A")=A1,"No Match"))
Dynamic array formulas.

I am getting a value error.
Also needing this to be run in VBA
Thank you for the direction though