Google sheets select field by maximum timestamp & specific cell value

Question

Here is an example google sheet for reference: https://docs.google.com/spreadsheets/d/1yxEog3c1FE2nNvcMeQ8sFy5GwVxc8A2-PSUYULh4czQ/edit?usp=sharing

On the first tab TokenBase in column C, I would like to find the maximum timestamp value (column A) from the TokenTransfers sheet where TokenTransfers Column D = TokenBase Column A. If there is no record on the TokenTransfers sheet, then TokenBase Column C should return TokenBase Column B.

So, for example, cell C2 should return the value "transfer2", because it has the largest timestamp for Token Number 1. Additionally, cell C9 should return the value "base" because Token Number 8 does not exist on the TokenTransfers sheet.

I have tried using basic IFERROR(VLOOKUP()) type codes and have also tried the google query language, but the query language returns more columns than I need.

Here is an example of a query I tried (you will find it in use on the TokenBase sheet in the example sheet I linked above):

=IFERROR(QUERY(TokenTransfers!A:D,"select A, C, D, max(A) where D="&A2&" group by A,C,D",1),B2)

I have also tried the method from this other question, but could not adapt it for my specific needs.

If anyone has any advice or guidance, it would be greatly appreciated! Answers do not need to be limited to just QUERY() functions.

Answer 1

try in C2:

=INDEX(IFNA(VLOOKUP(A2:A15, SORT({TokenTransfers!D2:D, TokenTransfers!B2:B}, TokenTransfers!A2:A, 0), 2, 0), B2:B15))

or:

=INDEX(IFNA(VLOOKUP(A2:A15, SORT({TokenTransfers!D2:D, TokenTransfers!C2:C}, TokenTransfers!A2:A, 0), 2, 0), B2:B15))