Sheets: Find first and last occurrence of a value in a range

user_4686515 0
Viewed 235

I have been struggling with the Google Sheets query for several hours and maybe getting confused how to combine HLookup and VLookup (or any other function) in a way that can find the first and last occurrence of a value in a sheet based on the date header above it.

Here is an example sheet for reference which is very clear, but I will try explain verbally as well ... https://docs.google.com/spreadsheets/d/1rBVM7EtW3IREundWs_f2ftic-h4fEB97u4k4sZyIFNY/edit#gid=0

Given that I have a 2d range of cafeteria locations serving food on certain day (so the Y-axis headers of the table are cateteria locations and the X-axis headers are dates and the value is the name of the food served that day such as "Pizza") ... I want to have another table below that has a lookup for the first and last date that the food was offered. In my reference sheet I denoted that by Yellow highlight.

It seems like something that should be doable in a spreadsheet tool; unless it is impossible and I am not realizing it. Is such an operation possible?

vlookup google-query-language flatten transpose google-sheets
2 Answers

See if this helps

=query(ArrayFormula(split(flatten(text(B1:E1, "yyyy-mm-dd")&"~"&B2:E5), "~")), "Select Col2, min(Col1), max(Col1) where Col2 <> '' group by Col2 label Col2 'Food', min(Col1) 'First Offered', max(Col1) 'Last Offered' format min(Col1) 'yyyy-mm-dd', max(Col1) 'yyyy-mm-dd'", 0)

Change range to suit.

user_38894230

delete range B10:C and use:

=INDEX(IFNA(VLOOKUP(A10:A, QUERY(SPLIT(FLATTEN(B1:E1&"×"&B2:E8), "×"), 
 "select Col2,min(Col1),max(Col1) group by Col2", ), {2,3}, 0)))

enter image description here

user_56326290
Related