2

I'm trying to find a way to get my minifs formula to work in an arrayformula in google sheets.

I have two sheets - Summary and Data. On Data, I have a list of names in Col A that duplicate. Col B has a list of labels that correspond to dates in Col C. On Summary, I have a query that is pulling a list of unique names from a third tab to Col A. In Col B, I'm trying to pull in the minimum date for each person that is not an arrival date.

Data

Col A         Col B            Col C
James       departure        1/1/2019
Sarah       arrival          12/3/2018
Sarah       departure        3/12/2019
David       departure        5/23/2019
James       departure        12/2/2018
David       departure        4/7/2019
David       arrival          11/18/2018

I know that inherently the min formula doesn't work in an arrayformula without some additional complex formula language that I admittedly don't have the skills to do.

The formula I'm using now is

=MINIFS(Data!C2:C,Data!A2:A,A2,Data!B2:B,"<>*arrival*")

This formula works as expected. I'd just really like to be able to use an arrayformula as my list of unique names constantly grows every day. I'd expect the output of the formula on Summary tab to be:

Col A           Col B
James         12/2/2018
David         4/7/2019
Sarah         3/12/2019

Link to example spreadsheet: https://docs.google.com/spreadsheets/d/1v_eXKkiPpZwmOSmB3CBkSx_usP05J7hKdZlecQ0--Wk/edit#gid=0

player0
  • 124,011
  • 12
  • 67
  • 124
Lindsay
  • 23
  • 3
  • Hi Lindsay and welcome to StackOverflow! It looks like you spent a while working on aligning your tables for the question - and while it looks nice, for future reference it's usually easier to format those kinds of things by wrapping your table in triple-backticks! That treats it as a code-block, which preserves whitespace :) You didn't do anything wrong - just a tip to help you out in the future! – JeremyW Jun 17 '19 at 21:02

1 Answers1

1
=ARRAYFORMULA(TO_DATE(IFERROR(VLOOKUP(A2:A, 
 SORT(QUERY(Data!A2:C, "where B = 'Departure'"), 1, 1, 3, 1), 3, 0))))

0

player0
  • 124,011
  • 12
  • 67
  • 124