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]

Not sure what you mean by that - could you possibly upload a screenshot what the problem is with the calculation?

 

[Dazzybeeguy]

Not sure what you mean by that - could you possibly upload a screenshot what the problem is with the calculation?

If the list of names changes, so that it goes from A2:A5 one day and another day it expands to A2:A15 how can i account for this in the formula, if i manually change it in the formula it works, if i change it to A2:A15 to cover all eventualities it doesn't work right unless the range is populated with names

 

[Domenic]

Try replacing...

names, A2:A5,

with

names, A2:INDEX(A:A, MATCH(REPT("z", 255), A:A, 1)),

Hope this helps!

 

[Domenic]

For what it's worth, here's a formula similar to the ones from both @hagia_sofia and @Scott R . . .

=LET(
    names, A2:INDEX(A:A, MATCH(REPT("z", 255), A:A, 1)),
    cities, B2:B26,
    criteria, "Liverpool",
    array, SCAN(
        0,
        SEQUENCE(ROWS(cities)),
        LAMBDA(i, a, COUNTIFS(TAKE(cities, a), criteria))
    ),
    indexes, MOD(array - 1, ROWS(names)) + 1,
    resultArray, IF(cities = criteria, INDEX(names, indexes), ""),
    resultArray
)

 

[Dazzybeeguy]

For what it's worth, here's a formula similar to the ones from both @hagia_sofia and @Scott R . . .

=LET(
    names, A2:INDEX(A:A, MATCH(REPT("z", 255), A:A, 1)),
    cities, B2:B26,
    criteria, "Liverpool",
    array, SCAN(
        0,
        SEQUENCE(ROWS(cities)),
        LAMBDA(i, a, COUNTIFS(TAKE(cities, a), criteria))
    ),
    indexes, MOD(array - 1, ROWS(names)) + 1,
    resultArray, IF(cities = criteria, INDEX(names, indexes), ""),
    resultArray
)

Thats great, thanks

 

[Scott R]

You just need to extend the 'names' range in the formula.

for my solution:

=LET( names, $A$2:$A$15,     IF( B2 = "Liverpool", INDEX( names, MOD( COUNTIF( B$2:B2, "Liverpool" ) - 1, ROWS( names )) + 1 ), "" ))

for Hagia's solution:

=LET(
city,"Liverpool",
names,A2:A15,
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,""))

 

[Domenic]

@Scott R

It looks like you may have missed this part of the original post...

. . . if i change it to A2:A15 to cover all eventualities it doesn't work right unless the range is populated with names

Hence the use of a dynamic reference so that the range will automatically adjust as new data is added and avoid having empty cells.

Cheers!