XLOOK formula giving correct result 90% of the time need it to tell me if there is more than one

[i0mega]

I have this formula to tell me the number of a school , when I put part of the name in G2.
Number is in E, school in F.
I need it to tell me if there is more than 1 if possible. ie I have Schoolname Infant, Schoolname Junior, Schoolname High. At the moment it is giving me the first one.

=XLOOKUP(G2&"*",F2:F500,E2:E500,,2)

Thank you

 

[RoryA]

You could use something like:

=IF(COUNTIF(F2:F500,G2&"*")>1,"Multiple matches",XLOOKUP(G2&"*",F2:F500,E2:E500,,2))

or you could use something like TEXTJOIN to return them all?

 

[i0mega]

This is brilliant thank you that work great. I am interested adding the text join, how would I do that?

 

[RoryA]

You could use something like this:

=TEXTJOIN(",",,FILTER(E2:E500,LEFT(F2:F500,LEN(G2))=G2,"No matches"))

 

[i0mega]

Thats just perfect, thank you so much - is there a solved button, I cant find one?

 

[RoryA]

Glad we could help. (looks like you figured out the Solved issue)