Google-sheets – In Google Sheets, find most recent comment for a domain

google sheetsgoogle-sheets-query

Using this sheet – https://docs.google.com/spreadsheets/d/1SFXBBlgX6KoffGqQ3HHsRmFcDurGiUjFLYXT3igy8Nk/edit?usp=sharing

On the Demo sheet, please help me display the most Recent Comment from the Log sheet.

As you can see in the sheet, currently I am using =QUERY(Log!$A$2:F,"Select E Where B='" & A2 & "' ORDER BY A Desc LIMIT 1",-1) but in a sheet this size (5000+ domains and 5000+ comments), its very inefficient and takes too long to update, breaking other connected sheets. Is there a way to make it more efficient?

Sample data

LOG sheet

+---------------------+--------------------+--+--+---------------------+
| Timestamp           | Domain             |  |  | Comment             |
+---------------------+--------------------+--+--+---------------------+
| 01/12/2019 00:00:00 | random domain 1454 |  |  | random comment 1944 |
+---------------------+--------------------+--+--+---------------------+
| 01/12/2019 00:00:00 | random domain 3245 |  |  | random comment 476  |
+---------------------+--------------------+--+--+---------------------+
| 01/12/2019 00:00:00 | random domain 3653 |  |  | random comment 4837 |
+---------------------+--------------------+--+--+---------------------+
| 01/12/2019 00:00:00 | random domain 3907 |  |  | random comment 1761 |
+---------------------+--------------------+--+--+---------------------+
| 12/12/2019 00:00:00 | random domain 1043 |  |  | random comment 3703 |
+---------------------+--------------------+--+--+---------------------+
| 12/12/2019 00:00:00 | random domain 1891 |  |  | random comment 958  |
+---------------------+--------------------+--+--+---------------------+
| 12/12/2019 00:00:00 | random domain 2821 |  |  | random comment 1590 |
+---------------------+--------------------+--+--+---------------------+
| 12/12/2019 00:00:00 | random domain 3005 |  |  | random comment 962  |
+---------------------+--------------------+--+--+---------------------+
| 12/12/2019 0:00:00  | random domain 3282 |  |  | random comment 1188 |
+---------------------+--------------------+--+--+---------------------+

DEMO sheet

+-----------------+------+--------------+----------+--------+--------------+---------------------+
| Domain          | Slug | Date Applied | Provider | Status | Payment type | Recent Comment      |
+-----------------+------+--------------+----------+--------+--------------+---------------------+
| random domain 1 |      |              |          |        |              | random comment 2821 |
+-----------------+------+--------------+----------+--------+--------------+---------------------+
| random domain 2 |      |              |          |        |              | random comment 639  |
+-----------------+------+--------------+----------+--------+--------------+---------------------+
| random domain 3 |      |              |          |        |              | random comment 1849 |
+-----------------+------+--------------+----------+--------+--------------+---------------------+
| random domain 4 |      |              |          |        |              | random comment 2257 |
+-----------------+------+--------------+----------+--------+--------------+---------------------+

Best Answer

={ARRAYFORMULA(ifna(VLOOKUP(A2:A1000, SORT({Log!B2:B, Log!A2:A, Log!E2:E}, 2, false), 3, 0)))}

Enter this formula is cell G2 of Sheet="Demo".

The logic is:

  • SORT: sorts the ranges of Logs by the Timestamp, descending. Note that the whole of the "Logs" data is NOT sorted;
    • "Domain" (Column B) (moved to the first column so it can be used with VLOOKUP),
    • "Timestamp" (Column A) (moved to the second column) and the column that is sorted,
    • "Comment" (Column E) which moved to the 3rd column.
  • VLOOKUP: lookup the "Domain" (Column A) in the sorted "Logs" range; returns the value in the 3rd column (Comment).
  • IFNA: returns a blank instead of an "#N/A" error message.
  • ARRAYFORMULA: enables the formula to be executed in rows 2 to 1000.

Props to @player0 for the initial solution in StackOvereflow

Arrayformula to find next value for criteria in Google Sheets

Related Topic