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)

Answers 1

  • 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.

Related Questions