Search code examples
google-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-querygs-vlookup

Google Sheets Formula Update

I have a spreadsheet here: https://docs.google.com/spreadsheets/d/1zh1EvjMqOnQVx4KAfHUEWT8_kCst1MOu0cJpUHb39q4/edit?usp=sharing

I am trying to have column I populate with the age that I have selected in the 2nd row of column C (per race). The formula I have now accommodates for each age, but isn't able to accommodate for the group that's called "LIT" in row 68 (Race 5).

Note - it also works for a special case where it's an "All star" race. see Row 243

=ARRAYFORMULA(IF(LEN(D3:D866),vlookup(row(A3:866)+REGEXMATCH(C3:C866&E3:E866,"^RACE.+\d$"),SORT({row(H3:H866)*(REGEXMATCH(C3:C866&E3:E866,"\d+s")),iferror(REGEXEXTRACT(C3:C866&E3:E866,"\d+s"))}),2,2)&" "&D3:D866&"s",))
Solution

  • try:

    =ARRAYFORMULA(IF(D3:D="",,IF(IF(ROW(A3:A) <= MAX(IF(D3:D<>"", ROW(A3:A))),
 VLOOKUP(ROW(A3:A), FILTER({ROW(A3:A), 
 {QUERY(IFNA(REGEXEXTRACT(C3:C, "LIT|\d+s|All Star")), "offset 1", 0); ""}}, LEN(
 {QUERY(IFNA(REGEXEXTRACT(C3:C, "LIT|\d+s|All Star")), "offset 1", 0); ""})), 2), )=
 "All Star", E3:E&" "&D3:D, IF(ROW(A3:A) <= MAX(IF(D3:D<>"", ROW(A3:A))),
 VLOOKUP(ROW(A3:A), FILTER({ROW(A3:A), 
 {QUERY(IFNA(REGEXEXTRACT(C3:C, "LIT|\d+s|All Star")), "offset 1", 0); ""}}, LEN(
 {QUERY(IFNA(REGEXEXTRACT(C3:C, "LIT|\d+s|All Star")), "offset 1", 0); ""})), 2), )&" "&D3:D)))

    0