0

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)

Improve this question
1
  • 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
    – Tedinoz
    Commented Apr 20, 2021 at 5:31

1 Answer 1

Reset to default
0

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.
Improve this answer
2
  • I especially appreciate the explanations. It's one thing to get a solution, but a whole other level to gain usable knowledge. Thank you!
    – Michael Nunya
    Commented 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.
    – Tedinoz
    Commented Apr 20, 2021 at 6:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.