0

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)

Sas
  • 51
  • 6
  • 1
    Please share an example screenshot of a desired result of "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" this is because MAXIF formula technically will return a single result & it is quite to hard to grasp/visualize your goal. Most importantly, please provide a sample workable sheet with sample data (it should be similar to your actual sheet, see https://stackoverflow.com/help/minimal-reproducible-example) to help us replicate what you want to achieve & to better understand the whole picture. – SputnikDrunk2 Jul 06 '21 at 22:16
  • share a copy/sample of your sheet with example of desired result – player0 Jul 06 '21 at 22:17

2 Answers2

1

try in C4:

={""; ARRAYFORMULA(IFNA(VLOOKUP({J5:J&" "&K5:K}, 
 VLOOKUP(FLATTEN(QUERY(TRANSPOSE(
 QUERY(J5:K, "select J,max(K) where J is not null 
 group by J label max(K)''")),,9^9)), {J:J&" "&K:K, F:F}, {1,2}, 0), 2, 0), 
 IFNA(VLOOKUP(J5:J, 
 QUERY({J5:K}, "select Col1,max(Col2) where Col1 is not null 
 group by Col1 label max(Col2)''"), 2, 0))))}

enter image description here

for the max you got it labeled and for the rest, its shown the max

UPDATE:

reverse of it:

={""; ARRAYFORMULA(IFNA(IF(""<>INDEX(SPLIT(VLOOKUP(J5:J&" ×"&K5:K, FLATTEN(QUERY(TRANSPOSE(QUERY({J5:J, "×"&K5:K}, 
 "select Col1,max(Col2) where Col1 is not null group by Col1 label max(Col2)''")),,9^9)), 1, 0), "×"),,2),
 INDEX(SPLIT(VLOOKUP(J5:J&" ×"&K5:K, FLATTEN(QUERY(TRANSPOSE(QUERY({J5:J, "×"&K5:K}, 
 "select Col1,max(Col2) where Col1 is not null group by Col1 label max(Col2)''")),,9^9)), 1, 0), "×"),,2),
 VLOOKUP(J5:J, TRIM(SPLIT(VLOOKUP(FLATTEN(QUERY(TRANSPOSE(QUERY({J5:J, "×"&K5:K}, 
 "select Col1,max(Col2) where Col1 is not null group by Col1 label max(Col2)''")),,9^9))&"*", 
 {J:J&" ×"&K:K&"×"&F:F}, 1, 0), "×")), 3, 0))))}

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
  • It looks like you've pretty much got it, player0. But my reading of the final goal seems to be opposite of what you have, i.e., I think the poster wanted max value returned for max-value rows only and F-label returned for non-max rows. – Erik Tyler Jul 07 '21 at 00:36
  • What @ErikTyler said... it's so very close, but for the data flip above. – Sas Jul 07 '21 at 00:42
  • @player0 I'm not sure what the issue is, but the results show up one line too high. That being said, I've selected the other answer since it's a lot simpler to follow. Thank you for your attempts to help; I appreciate your time! – Sas Jul 07 '21 at 15:31
  • Thanks for the update, but I've chosen to go with the significantly simpler route proposed by @ErikTyler. Thanks again! – Sas Jul 08 '21 at 01:46
0

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)))})

Erik Tyler
  • 9,079
  • 2
  • 7
  • 11
  • So close - but instead of returning label/F of its own row, I'm trying to return label/F of the row of the max value (see screen shots above) – Sas Jul 07 '21 at 02:00
  • See the addendum in my post. For future reference, it is always the most efficient use of time if you post a link to a spreadsheet with a short set of realistic data, and the results you want manually entered in the location you'd like to see those results. – Erik Tyler Jul 07 '21 at 02:18
  • This is perfect, and (relatively) simple to understand. Thank you for your help! – Sas Jul 07 '21 at 15:28