Formula to construct filtered dynamic arrays

[Robert_wood]

I would like to have an efficient way to construct a dynamic array from existing data. Assume Sheet1 with data having column headers and Sheet2 used to focus on a column of data in Sheet1. Currently I have the following formulas in Sheet2:

    A2=MATCH($A$1, Sheet1!1:1, 0))

    A3=SUBSTITUTE(ADDRESS(1, $A$2, 4), "1", ""))

    A4="Sheet1!$" & $A$3 & ":$" & $A$3)

After the user enters a valid identifier in Sheet2!A1, A4 will contain something like 'Sheet1!D:D'. Then to prevent spillage from translating blank cells into cells containing 0, I use:

    B1=FILTER(INDIRECT($A$4), ISNUMBER(INDIRECT($A$4))+ISTEXT(INDIRECT($A$4)))

Is there a more efficient way to construct this filtered array without using or with less use of helper cells (A3 and A4)?

BACKGROUND

I have a worksheet (Sheet1) with over 100 columns and over 7500 rows. Row 1 is populated by header information. Both the number of columns and rows can vary over time. The number of items (rows) in each column vary from other columns. Sheet1 is populated by an external program (python) and its normal activity is to effectively push all rows other than row 1 down by one and add a new row for each column. The user interface to these data are to enter a string into a cell in a second worksheet (Sheet2!$A$1) that matches one of the headers in row Sheet1!1:1. From this the dynamic array is constructed as explained above.

[#2]

@Robert_wood 

Best way is to either:

a) have the Python program modified to ensure there is a Table containing the data on Sheet1, or

b) make the Python-produced data on Sheet1 into a Table after it's produced

That way, finding and returning a column is easy. Suppose the column name you want to retrieve is in cell $A$1 and the table is called "PythonData", then:

=FILTER(PythonData,PythonData[#Headers]=$A$1)

[#3]

@flexyourdata, I like your suggestion. Very powerful. However, it causes blank cells in the table to be replicated as cells with a value of 0 in the result. This makes functions like AVERAGE to fail.

[#4 · Accepted solution]

@Robert_wood How about this one then?

=LET(
    filtered, FILTER(
        PythonData,
        PythonData[#Headers] = $A$1
    ),
    FILTER(filtered, filtered <> "")
)

[#5]

@Riny_van_Eekelen

Wow. Perfect. It solves the problem. No helper cells needed.