CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-querygs-vlookup
Lindsay
Lindsay

Reputation: 23

Arrayformula to pull the minimum value in a column based on cell contents of another column

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

Upvotes: 2

Views: 1312

Answers (1)

player0
player0

Reputation: 1

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

0

Upvotes: 1

Related Questions