1

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 |
+-----------------+------+--------------+----------+--------+--------------+---------------------+
Improve this question
2
  • 1
    Welcome to Web Applications. All the relevant details to answer the question should be included in the question body, i.e. the structure of the Log sheet and some sample data.
    – Rubén ♦
    Jul 28, 2020 at 3:00
  • @Rubén I agree. The question should (ideally) reflect all the information, and not rely on viewing in a spreadsheet, though I never knew how this might be done Via the meta, I found tablesgenerator, and I think it does a good job. For information of others, pasted from spreadsheet, generated online, copied to clipboard, pasted into question, then used markdown "pre-formatted" command.
    – Tedinoz
    Jul 29, 2020 at 23:38

1 Answer 1

Reset to default
1

={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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.