# How to find and get data from only a certain, dynamic set of columns in another Sheet?

I saw a proposed solution here, but it didn't work for me.
How to use arrayformula to find and get data from only a certain, dynamic set of columns?
Using formula:

``````=SUM(INDIRECT(ADDRESS(4, MATCH("BB", 3:3, 0), 4)&":"&
SUBSTITUTE(ADDRESS(4, MATCH("BB", 3:3, 0), 4), "4", "")))
``````

I am trying to do something similar but it's not working ("Formula parse error")
Differences from the original (above):

• I'm trying to do an average, not a sum.
• The data to compare and average are both on another Sheet.

I want to be able to average a column of data below a header from another Sheet,
if the header on that Sheet ('Vends'!) matches the cell to the left on the current Sheet ('AGG-Vends'!).
Eg. If the header on row 2 of Sheet Vends! matches A2 on Sheet 'AGG-Vends'!,
I want to average all the values below that header (row 3 and so on,
with an indeterminate number of values to be added).

My headers on Vends! Sheet are on row 2, so I modified the row number from which to start searching, to row 3.

``````=AVERAGE(INDIRECT(
ADDRESS(3, MATCH(A2, Vends!A2:2, 0), 4, TRUE, Vends!)&":"&
SUBSTITUTE(
ADDRESS(3, MATCH(A2, Vends!A2:2, 0), 4, TRUE, Vends!),
"3", "")))
``````

My formula is in B2 on "AGG-Vends'! Sheet (highlighted Blue). sample Sheet

(Vends may be added or moved around, etc., so I was attempting to circumvent any Sheet-wide catastrophes of having to remodd formulas to fix)

• Welcome. I found it interesting to note that although the referenced question assumed the use of arrayformula, the answer (which wasn't accepted, BTW) does NOT use arrayformula Apr 20, 2021 at 5:31

Enter this formula in sheet: AGG-Vends, cell B2:

`=AVERAGE(INDIRECT(ADDRESS(3, match(A2,Vends!\$2:\$2,0), 4,1,"Vends")&":"& SUBSTITUTE(ADDRESS(3, match(A2,Vends!\$2:\$2,0), 4), 3, "")))`

Your formula was close though there are several points of difference:

• `match(A2,Vends!\$2:\$2,0)`: the row reference for the headers must be absolute, otherwise as the formula is copied down the row number will increment.

• `ADDRESS(3, match(A2,Vends!\$2:\$2,0), 4,1,"Vends")`: The syntax for `ADDRESS` requires that the sheet should be "text indicating the name of the sheet into which the address points".

• the sheet name should be supplied in double quotation marks, and
• the exclamation point is not required. The function will supply this.
• `SUBSTITUTE(ADDRESS(3, match(A2,Vends!\$2:\$2,0), 4), 3, "")`: The output for this element is the column letter.

• no sheet name is supplied
• the row number (3) is the value to be searched for. I found that it wasn't necessary to put this in quotes.
• I especially appreciate the explanations. It's one thing to get a solution, but a whole other level to gain usable knowledge. Thank you! Apr 20, 2021 at 5:39
• I've thought about whether this formula could be made more efficient/dynamic, perhaps using a query. But a stumbling block is that you selection names are in rows and the data is arranged by Column. There probably is a better solution, but not one that I can think of for the moment. Apr 20, 2021 at 6:08