Given a table of names and surnames, here is the link:

https://docs.google.com/spreadsheets/d/13IyzK_Wa-AuIUG4iiXh2hD8g4orikKB-eo-vEz6qIss/edit?usp=sharing

It is necessary to find the names of all people with that name by their last name. It is easy to find at least one using VLOOKUP [C1] or a formula from [B1]. Formulas from [B2], [B3], [B4] and [B5] are looking for just what they need - they are looking for 1E 2E 3E and 4th names, respectively. Formula drawn from the source .

Help to understand why and how the formula works, and its component parts in column C do not work.

    1 answer 1

    It is necessary to find the names of all people with that name by their last name.

    This is a filtering task, not a search. And it is solved by one formula:

    =filter(B:B, A:A = "Ivanova")