Extract list and ignore blanks based on criteria (names from a drop down)

[TME1993]

Please help with the following!!

Sheet1 is the dataset containing names and information related to those names. In Sheet2 I want to reference a particular name (drop down list) and pull a new list with the relevant info (age, fav food, fave sport, etc.) while ignoring the blank columns. In other words, create a new list with just cells that have information in them.

Thank you!!

 

[Peter_SSs]

Welcome to the MrExcel board!

Using your 2021 version, try this

TME1993.xlsm
	A	B	C	D	E
1	Name (select)	Name	Age	Favourite Sport
2	Jim	Jim	22	Hockey
Sheet2

Cell Formulas
Range	Formula
B1:D2	B1	=LET(f,FILTER(Table1[#All],(Table1[[#All],[Name]]=A2)+(Table1[[#All],[Name]]=Table1[[#Headers],[Name]])),FILTER(f,INDEX(f,2,0)<>""))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
A2	List	=INDIRECT("Table1[Name]")

If you need the results in a version that does not have the FILTER function you could try this instead

TME1993.xlsm
	A	B	C	D	E	F
1	Name (select)	Idx	Name	Age	Favourite Sport
2	Jim	1	Jim	22	Hockey
Sheet2 (2)

Cell Formulas
Range	Formula
C1:F1	C1	=IFERROR(INDEX(Table1[#Headers],AGGREGATE(15,6,SEQUENCE(,COLUMNS(Table1))/(INDEX(Table1,$B2,0)<>""),COLUMNS($C:C))),"")
B2	B2	=MATCH(A2,Table1[Name],0)
C2:F2	C2	=IFERROR(INDEX(INDEX(Table1,$B2,0),AGGREGATE(15,6,SEQUENCE(,COLUMNS(Table1))/(INDEX(Table1,$B2,0)<>""),COLUMNS($C:C))),"")

Cells with Data Validation
Cell	Allow	Criteria
A2	List	=INDIRECT("Table1[Name]")