Have you ever needed to find the largest date in each group in Google Sheets? If so, you may have come across the Large function. But here’s the catch – the Large function can only extract the largest date in a limited way. So, what’s the alternative solution?
Vlookup + Sort combo to Find the First Largest Date in Each Group in Google Sheets
In a group of data, you can use the Vlookup function together with Sort to return the largest date in each group. However, this combination can only return the first largest date in each group. But don’t worry, I have a formula that can fulfill your requirement.
Let’s consider an example. Suppose you have a list of products with their corresponding dates. Using the formula =ArrayFormula(IFERROR(vlookup(D2:D,Sort({B2:B,A2:A},2,0),2,0))), you can easily extract the first largest date in each group.
But what if you want to find the second or third largest date in each group? I have a formula for that too!
Array Formula to Find the Nth Largest Date in Each Group in Google Sheets
I have a combination formula that can find the largest dates irrespective of their nth largest element. This means you can use it to return the first, second, or third largest dates in Google Sheets.
The formula is a bit complex, so let me break it down for you step by step:
Step # 1 (Cell G2) – Sorting Products and Dates
Sort the range A2:B using the SORT function. First, sort the products (column 2) in descending order, and then sort the dates (column 1) in descending order. The formula is: =sort(A2:B,2,0,1,0)
Step # 2 (Cell J2) – Extracting the Second Column
Using the Index function, return the second column (B2:B) from the sorted output. The formula is: =index(sort(A2:B,2,0,1,0),0,2)
Step # 3 (Cell L2) – New Virtual Third Column Containing Running Count
Count the products and put the sequential number group-wise. If a product “Apple” repeats twice, it will return the number 1 to the first occurrence and 2 to the second occurrence, and so on. The formula is: =ARRAYFORMULA(COUNTIFS(B2:B,B2:B,ROW(B2:B),"<="&ROW(B2:B)))
Step # 4 (Cell N2) – Sorted Range in Running Count
Replace the range B2:B in the above COUNTIFS formula with the Step # 2 formula. The formula is: =ARRAYFORMULA(COUNTIFS(index(sort(A2:B,2,0,1,0),0,2),index(sort(A2:B,2,0,1,0),0,2),ROW(B2:B),"<="&ROW(B2:B)))
Step # 5 (Cell P2)
Combine Step # 1 and Step # 4 formulas to create a 3-column array. The generic formula is: =ArrayFormula({step#1 formula,step#2 formula}). The specific formula is: =ArrayFormula({sort(A2:B,2,0,1,0),COUNTIFS(index(sort(A2:B,2,0,1,0),0,2),index(sort(A2:B,2,0,1,0),0,2),ROW(A2:A),"<="&ROW(A2:A))})
Step # 6 (Cell T2) – Filter the Nth Largest Date in Each Group
Use the Step # 5 data as the range in the Query function and filter column 3 to select the nth largest value. The formula is: =ArrayFormula(Query({sort(A2:B,2,0,1,0),COUNTIFS(index(sort(A2:B,2,0,1,0),0,2),index(sort(A2:B,2,0,1,0),0,2),ROW(A2:A),"<="&ROW(A2:A))},"Select Col2,Col1 where Col3=1"))
Finally, in cell E2, replace the code Sort({B2:B,A2:A},2,0) in the formula =ArrayFormula(IFERROR(vlookup(D2:D,Sort({B2:B,A2:A},2,0),2,0))) with the Step # 6 formula. The formula becomes: =ArrayFormula(IFERROR(vlookup(D2:D,Query({sort(A2:B,2,0,1,0),COUNTIFS(index(sort(A2:B,2,0,1,0),0,2),index(sort(A2:B,2,0,1,0),0,2),ROW(A2:A),"<="&ROW(A2:A))},"Select Col2,Col1 where Col3=1"),2,0)))
And voila! You can now extract the largest date in each group in Google Sheets, whether it’s the first, second, or third largest date.
For more useful Google Sheets formulas and tips, check out Crawlan.com.
Happy date extracting!
