CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
nachocab
nachocab

Reputation: 14364

How to get the first and last value that match condition in Google Sheets?

I have a table in this format:

enter image description here

I would like to get the first and last date (column A) that hasn't been invoiced (column E) so I can later concatenate: ="Invoice for items between " & FIRST_DATE & " and " & LAST_DATE

I've tried using VLOOKUP but it appears that "" isn't interpreted as an empty match.

Upvotes: 1

Views: 1138

Answers (1)

MattKing
MattKing

Reputation: 7773

I would think this would work.

=TEXT(MIN(FILTER(1*LEFT(A2:A;10);A2:A<>"";E2:E=""));"yyyy-mm-dd")

and

=TEXT(MAX(FILTER(1*LEFT(A2:A;10);A2:A<>"";E2:E=""));"yyyy-mm-dd")

Your timestamps are coming from a system other than GoogleSheets. They need to be "forced" into numbers before they can be MIN()'d and MAX()'d. That's what 1* LEFT(... , 10) does.

Then they need to get turned back into text before they're concatenated in a text string like your plan. That's what TEXT(... ,"yyyy-mm-dd") does.

Upvotes: 3

Related Questions