XLOOKUP in cells with multiple rows of data

[kevindj71]

Is there a formula tweak for XLOOKUP to look in all rows of data within a cell? In attached example, I'd want to be able to find 47348 and/or 47349, etc. instead of it just looking at the first value in the cell (47345).

 

[Fluff]

You can use the * as a wild card

 

[kevindj71]

You can use the * as a wild card

Thanks, but I have tried a wildcard a few different ways. Where would it go in the attached screenshot in the Lookup_array?

 

[Fluff]

Can you post your actual formula.

 

[kevindj71]

Can you post your actual formula.

=XLOOKUP(@[User Name],Oct1521!C[6],Oct1521!C[2])

 

[Fluff]

Ok, try

=XLOOKUP("*"&@[User Name]&"*",Oct1521!C[6],Oct1521!C[2],,2)

 

[kevindj71]

Ok, try

=XLOOKUP("*"&@[User Name]&"*",Oct1521!C[6],Oct1521!C[2],,2)

Beautiful, thanks so much!

 

[Fluff]

Glad to help & thanks for the feedback.

 

[kevindj71]

Glad to help & thanks for the feedback.

This is working absolutely great, but I'm curious now if we can add an additional wrinkle. Now I believe the wildcard search stops at the first "find", can the search continue and return additional "finds"?

 

[Fluff]

Xlookup along with other lookup functions always returns the 1st matching value.