1

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

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

enter image description here

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 2

0

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))))
0

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.

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.