1

Spreadsheet Im working on: https://docs.google.com/spreadsheets/d/1rTzdmKVMqD8sz1D639y5qgvXNBcWaAuBaQOAmwJXdA4/edit#gid=0

I need a formula that returns the Comment for the Domain that equals the search key: with the most recent Timestamp when Timestamp is unsorted.
You can see in my example I have tried using both INDEX and VLOOKUP but with no success.

Actual spreadsheet has over 5000 rows and multiple sheets, and is quite complex, therefore a QUERY statement is too inefficient and causes the sheet to crash.

Improve this question

1 Answer 1

Reset to default
0

EDIT

(following OP's comment)

Unfortunately I hadn't put quite enough detail in. This only returns the most recent comment, not the most recent comment for the domain I am looking for - I have edited the question with the extra details.

In this case you should also take advantage of the QUERY function

=VLOOKUP(MAX(QUERY(A1:F,"where B=' "&H7&" ' ")),A1:F,5,0)

where H7 is the cell containing the domain you are looking for.

Please use the following formula:

=VLOOKUP(MAX(A1:A),A1:F,5,0)

enter image description here

Functions used:

Improve this answer
7
  • Thanks for the attempt! Unfortunately I hadn't put quite enough detail in. This only returns the most recent comment, not the most recent comment for the domain I am looking for - I have edited the question with the extra details.
    – tzonov
    Jul 27, 2020 at 0:39
  • Thanks again - I have edited to include details as to why a QUERY will not work.
    – tzonov
    Jul 27, 2020 at 11:03
  • By continuously editing your question and adding new parameters you have completely changed the original question. This can be very confusing for future users of the site. In any case, to lessen the load of the query function you can use =VLOOKUP(MAX(QUERY(A1:B,"where B='"&H7&"' ")),{A1:A,E1:E},2,0). Still. I would suggest you accept this answer and if still in need, create a new question.
    – marikamitsos
    Jul 27, 2020 at 16:24
  • 1
    Marked as solved. I edited to add more detail that I didn't realise would be necessary, in the hope it will also less confusing for future users, which is certainly my intent. Thanks for your assistance.
    – tzonov
    Jul 27, 2020 at 23:42
  • 1
    A pleasure. I did ask a new question, hopefully with the new data it will be simpler to answer.
    – tzonov
    Jul 28, 2020 at 2:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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