Index / Match / Jump to next column if value already used

Hi!

I tried to search the forum for a result but without any luck for this particular case.

Please see attached spreadsheet. Is there any solution to bring in cell B10 "Daniel" instead "Pascal" (again)? The solution/adjustment should be a single formula (meaning no additional helper column, etc.).

Many thanks,
FixandFoxi

Thanks for this but the "-1" does not fit the requirements, because the "same Raninkg" is not always the one on the next row. The final spreadsheet has a few hundred rows...! The formula should really jump to the next matching value, regardless of any other rows in between.

I forgot to mention, that the current formula approach (meaning INDEX and MATCH) should remain the same (e.g. VLOOKUP is no alternative).

Any other ideas?

Hi Marcol! Thanks, this could be an alternative solution. However, any idea to do this without a helper column?

Thanks for all solutions. I prefer the one from Marcol (Tie Breaker).