# How to return a value from a row from the last column a text appears

I am having problems returning the correct value i need. I enter data on this table.

And i want to return some values on the other table.

In that second table i want to return the last date his name appear from the columns in first table. Example: Last column Akar appeared on 23.06.2022, But Aphrodiette last appeared on 12.06.2022.

I tried HLOOKUP but it always return 23.06.2022.
`=HLOOKUP(A5,'Shadow War Battles'!I:I,1,TRUE)`
I think I need to enter MATCH and INDEX but I can't understand what exactly I need to enter.
The columns will continue to be filled to the right side and there are blank cells in those columns too.

I am sure it is easy solution but I guess my brain overthink this. Can you help me?

## 2 Answers

Try this out. It should return the latest date and total instances for that name. If it works out, I can break down how it works.

``````=ARRAYFORMULA(
IF(ISBLANK(A3:A),,
IFERROR(
VLOOKUP(
A3:A,
QUERY(
SPLIT(
FLATTEN(
IF(ISBLANK('Shadow War Battles'!A3:I),,
'Shadow War Battles'!A3:I&"|"&'Shadow War Battles'!A1:I1)),
"|"),
"select Col1, Count(Col2), Max(Col2)
where Col2 is not null
group by Col1
order by Max(Col2) desc",0),
{2,3},FALSE))))
``````

WOW that worked. So you made it to count the names also. Impressive. You are a genius. :) That was way beyond my understanding of formulas.
I don't know what those functions do: Query, Split, Flatten and also the last part:

``````select Col1, Count(Col2), Max(Col2)
where Col2 is not null
group by Col1
order by Max(Col2) desc",0),
``````

Thank you for the help. I would love some explanation.