Formula: =CHOOSEROWS(FILTER(A2:A24, B2:B24 = F4), (COUNTA(FILTER(A2:A24, B2:B24 = F4))-1))
=CHOOSEROWS(FILTER(A2:A24, B2:B24 = F4), (COUNTA(FILTER(A2:A24, B2:B24 = F4))-1))
This formula uses functions such as FILTER(to get values matching the name) and CHOOSEROWS to to get the desired outcome.
name
Sample Output
References: CHOOSEROWS, FILTER