Search code examples
google-sheetsarray-formulasmaxifs

Looking for ArrayFormula specification for MAXIFS where criteria is based on unlimited values in another column

I have a gsheet with columns out to AC, most of which are filtered from another sheet which is, in turn, imported from an external source. The relevant columns are:

  • C: where I want the found value to be (formula in C4, arrayformula populating the remainder of C)
  • F: row label (this is a unique alphanumeric value)
  • J: comparison keys (only compare key "a" to other rows with key "a", key "b" to other key "b", etc). Some of these may be blank. Non-blank keys are either one or two alpha numeric characters. Ideally when J is blank, C will also be blank.
  • K: a numeric value for the row. Values in K are non-unique and non-sorted.

Row 3 is header information Row 4 (a hidden row) is where my ArrayFormulas are located for other columns Row 5 is where data begins (and continues for several hundred additional rows) For each key found in column J, I want to identify the largest value of K and return it in C, and I want to do this using an ArrayFormula, so that I only need to specify it once at the top.

Example set for primary goal Example set for stretch goal

What I've come up with so far, for the primary, is

=ArrayFormula(maxifs(K4:K,J4:J,"<>"&"",J4:J,"="&J4))

which, of course, did not work as expected. I have not even begun to consider the stretch goal (since I'd have modified the other after it worked... which it isn't)

  1. Primary importance: getting the arrayformula to work and to return K/value into C
  2. Stretch Goal: the above plus returning K/value ONLY for the maxed line (or any maxed line if there are multiple identical K/values) and (in place of K-value) return F/label for the non-maxed lines. Translation: show the value for the max and for all the non-maxed, show the label of the line that is maxed (not the label of the existing line)

Any recommendations?

(edit to put in images of data set instead of the oddly formatted stuff that didn't actually work)

Solution

  • Try this in C4 of an otherwise empty C4:C:

    =ArrayFormula({"Header"; IF(J5:J="",,IF(VLOOKUP(J5:J,SORT(J:K,2,0),2,FALSE)=K5:K,K5:K,F5:F))})

    You can change the header in the formula as you like.

    The rest basically says this: "If J is null, return null. Otherwise, look up J in J:K reverse-sorted by K (i.e., highest values at the top). If what is found matches what is already in the row, return K; otherwise, return F."

    ADDENDUM (based on updated information in original post):

    =ArrayFormula({"Header"; IF(J5:J="",,IF(VLOOKUP(J5:J,SORT(J:K,2,0),2,FALSE)=K5:K,K5:K,VLOOKUP(J5:J,SORT({J:K,F:F},2,0),3,FALSE)))})