Lookup multiple columns and return multiple values of rows that have data

[muhleebbin]

Is it possible to look up within multiple columns and return the rows that have data if that column is selected in another field?

Example data would be something like columns c1-z1 having values 1-24. If rows 2-100 had 'x' or any other value indicating they fit that column, would it be possible to have sheet 2 cell a1 have a drop down menu of 1-24 and whichever it selects bring up the rows that actually fit that selected column?

 

[Toadstool]

Hi Muhleebbin,

Here is the data sheet1 (with columns 6 to 24 and rows 10 to 197 hidden for brevity).

Muhleebbin.xlsx
	C	D	E	F	G
1	1	2	3	4	5
2
3		x			x
4
5			x
6			x		x
7				x
8					x
9					x
198	x	x	x	x	x
199					x
200					x
Sheet1

Here's the selection in Sheet2 (note that column C formula should be copied down to row 200)

Muhleebbin.xlsx
	A	B	C
1	Column		Row
2	5		3
3			6
4			8
5			9
6			198
7			199
8			200
9
10
Sheet2

Cell Formulas
Range	Formula
C2:C10	C2	=IFERROR(AGGREGATE(15,6,ROW(Sheet1!$C$2:$Z$200)/((Sheet1!$C$2:$Z$200="x")*(COLUMN(Sheet1!$C$2:$Z$200)=MATCH($A$2,Sheet1!$C$1:$Z$1,0)+COLUMN(Sheet1!$B$1))),ROW()-ROW($C$1)),"")

Cells with Data Validation
Cell	Allow	Criteria
A2	List	=Sheet1!$C$1:$Z$1

 

[muhleebbin]

Hi Toadstool,

Thank you very much! That worked perfectly!

If I were to populate the data with lettering instead of 'x', would this still be possible? If the column titles are countries and I populated with S=self, Fa=Family or Fr=Friends rather than just 'x', that would eliminate many columns and help immensely. I believe there will be those options plus '+' or '-' indicating interest or lackthereof and possibly some new ones as I continue this project. Ultimately it would look in the column selected and then bring up any row that has a populated value. I could then use an index/match to find the actual value.

I apologize for the change but your formula just gave me this secondary idea.

 

[Toadstool]

Yes, it's a straightforward change.

Here's the modified data in Sheet1 but I'm just showing a few rows and columns.

Muhleebbin2.xlsx
	C	D	E	F	G	H	I
1	France	Spain	Belgium	UK	USA	Italy	Greece
2
3		S			S+
4							S
5			Fa-				Fa
6			Fr		Fa+
7				S-
8					Fr+
9	S	Fa+	S-	Fa-
198
Sheet1

Here's the modified Sheet2

Muhleebbin2.xlsx
	A	B	C	D
1	Column		Row	Content
2	USA		3	S+
3			6	Fa+
4			8	Fr+
5
6
Sheet2

Cell Formulas
Range	Formula
C2:C6	C2	=IFERROR(AGGREGATE(15,6,ROW(Sheet1!$C$2:$Z$200)/((Sheet1!$C$2:$Z$200<>"")*(COLUMN(Sheet1!$C$2:$Z$200)=MATCH($A$2,Sheet1!$C$1:$Z$1,0)+COLUMN(Sheet1!$B$1))),ROW()-ROW($C$1)),"")
D2:D6	D2	=IFERROR(INDEX(Sheet1!$C$2:$Z$200,C2-ROW(Sheet1!$C$1),MATCH($A$2,Sheet1!$C$1:$Z$1,0)),"")

Cells with Data Validation
Cell	Allow	Criteria
A2	List	=Sheet1!$C$1:$Z$1

 

[muhleebbin]

Thank you very much for your help here! It works just as needed!