VLOOKUP Array Returns

There I was, frustrated that using GROUP BY ORDER BY together in Google Sheets QUERY for differing columns isn’t practical. And instead of searching Google for solutions I used before. I asked ChatGPT.

Boy, oh boy, was I surprised by the answer.

Stepping back, the intent was to SELECT A, B, C GROUP BY A ORDER BY C DESC. Where A is email, C is a date, and B is a number. In SQL, no problem; for Google Sheets, a significant problem.

Asking ChatGPT Given the uploaded data, using Google Sheets, how do I use the QUERY function to get the latest entry for column A where column B is the date? responds with its assumptions.

And then presents the formula, =QUERY(A:B, "SELECT A, B WHERE B = DATE '" & TEXT( MAX( ARRAYFORMULA( DATEVALUE(B2:B))), "yyyy-mm-dd") & "' LIMIT 1", 0).

However, that’s a single result response to the most recent information.

Not being daunted, I let ChatGPT know I would like the latest entries for all of the emails, not a single result.

ChatGPT responds, letting me know about Google Sheets’ difficulty handling this, yet here are a few formulas to get what’s desired. And it’s what’s in the procedures that I learned something new.

1. Get Unique Emails: =UNIQUE(A2:A) Put this in, say, cell F2. This will give you a list of unique email IDs.
2. Get Latest Date for Each Email: =ARRAYFORMULA(IF(F2:F="", "", VLOOKUP(F2:F&" ", {A2:A&" ", B2:B}, 2, 0))) Put this in, say, cell G2. This will get the latest date for each email.
3. Get Full Data for Each Email and Latest Date Combination:
   excel =ARRAYFORMULA(IF(F2:F="", "", VLOOKUP(F2:F&" "&G2:G, {A2:A&" "&B2:B, A2:C}, {1, 2, 3}, 0)))
   Place this in, say, cell H2. This will give you columns for Email ID, Date, and Amount for each email’s latest date. You can expand the {1, 2, 3} to {1, 2, 3, 4, 5} if you want the Currency and Reason columns as well.

Dropping the above formulas into my sheet and tweaking a little, I have a list of emails with the latest numbers associated, as desired.

Yet, with this solution, I recalled VLOOKUP can use a custom-built array to create an email and date string to compare for selection, which I do occasionally.

More importantly, and what really was the reason for all of this, is that VLOOKUP can return an array of values, not just a singular.

Despite all of my time using ARRAYFORMULA with VLOOKUP, I never knew.

Suddenly, in one data processing sheet alone, using a VLOOKUP array, the I drop five columns of formula processing leading to a faster-responding spreadsheet.

Thanks, ChatGPT, for today’s win and learn.

PS ChatGPT will explain formulas too. Just ask it.