Assign next in list if a criteria is met. See Beatles example

[Dazzybeeguy]

Column A2:A6 is the names
B:B is a list of cities
In Column C in C2 down I want a formula that would look to see if the City is Liverpool and if so assign it to the 1st person in the list of names, on the next row if its Liverpool assign that to the next person and so on.

Names	Cities	Results list
John	Liverpool
George	Leeds
Paul	Luton
Ringo	Manchester
	Burnley
	Liverpool	John
	Liverpool	George
	Chester
	York
	Liverpool
	London
	Hull
	Liverpool	Paul
	Leeds
	Liverpool	Ringo
	Luton
	Liverpool	John
	Liverpool	George
	Leeds
	Luton
	Manchester
	Burnley
	Liverpool	Paul
	Liverpool	Ringo
	Chester

 

[hagia_sofia]

Hello, test this:

=LET(
city,"Liverpool",
names,A2:A5,
a,B2:B26,
r,ROWS(names),
b,SCAN(0,SEQUENCE(ROWS(a)),LAMBDA(x,y,COUNTIFS(TAKEa,y),city))),
c,IF(a=city,b,""),
d,TOCOL(IF(SEQUENCE(,MAX(b)/r+1),names),,TRUE),
e,SEQUENCE(ROWS(d)),
XLOOKUP(c,e,d,""))

 

[Dazzybeeguy]

Hello, test this:

=LET(
city,"Liverpool",
names,A2:A5,
a,B2:B26,
r,ROWS(names),
b,SCAN(0,SEQUENCE(ROWS(a)),LAMBDA(x,y,COUNTIFS(TAKEa,y),city))),
c,IF(a=city,b,""),
d,TOCOL(IF(SEQUENCE(,MAX(b)/r+1),names),,TRUE),
e,SEQUENCE(ROWS(d)),
XLOOKUP(c,e,d,""))

I get the error you have entered too few arguments and it highlights city
=LET(
city,"Liverpool",
names,A2:A5,
a,B2:B26,
r,ROWS(names),
b,SCAN(0,SEQUENCE(ROWS(a)),LAMBDA(x,y,COUNTIFS(TAKEa,y),city))),
c,IF(a=city,b,""),
d,TOCOL(IF(SEQUENCE(,MAX(b)/r+1),names),,TRUE),
e,SEQUENCE(ROWS(d)),
XLOOKUP(c,e,d,""))

 

[hagia_sofia]

Does it happen in Excel 365?

 

[Dazzybeeguy]

Does it happen in Excel 365?

Yes

 

[hagia_sofia]

It seems that there was a missing parenthesis (my bad):

=LET(
city,"Liverpool",
names,A2:A5,
a,B2:B26,
r,ROWS(names),
b,SCAN(0,SEQUENCE(ROWS(a)),LAMBDA(x,y,COUNTIFS(TAKE(a,y),city))),
c,IF(a=city,b,""),
d,TOCOL(IF(SEQUENCE(,MAX(b)/r+1),names),,TRUE),
e,SEQUENCE(ROWS(d)),
XLOOKUP(c,e,d,""))

 

[Dazzybeeguy]

It seems that there was a missing parenthesis (my bad):

=LET(
city,"Liverpool",
names,A2:A5,
a,B2:B26,
r,ROWS(names),
b,SCAN(0,SEQUENCE(ROWS(a)),LAMBDA(x,y,COUNTIFS(TAKE(a,y),city))),
c,IF(a=city,b,""),
d,TOCOL(IF(SEQUENCE(,MAX(b)/r+1),names),,TRUE),
e,SEQUENCE(ROWS(d)),
XLOOKUP(c,e,d,""))

Thanks that does the trick

 

[hagia_sofia]

Thanks for the feedback and sorry for that mistake...

 

[Scott R]

Or this in C2 for a non-dynamic array formula that would need to be copied down:

=IF( B2 = "Liverpool", INDEX( $A$2:$A$5, MOD( COUNTIF( B$2:B2, "Liverpool" ) - 1, 4 ) + 1 ), "" )

 

[Dazzybeeguy]

It seems that there was a missing parenthesis (my bad):

=LET(
city,"Liverpool",
names,A2:A5,
a,B2:B26,
r,ROWS(names),
b,SCAN(0,SEQUENCE(ROWS(a)),LAMBDA(x,y,COUNTIFS(TAKE(a,y),city))),
c,IF(a=city,b,""),
d,TOCOL(IF(SEQUENCE(,MAX(b)/r+1),names),,TRUE),
e,SEQUENCE(ROWS(d)),
XLOOKUP(c,e,d,""))

I just noticed one issue, if I add names to the list so names so I change names from A2:A5 to A2:A15 it doesnt work properly it misses loads out, what else needs to change in the formula to fix this ?