Multitable Lookup Array - Xlookup?

[ithoughtiknewit]

Hi! I think this should be easier than I am making it. I have a list of customers and several employees visiting them. I then have the list of employees, and I want a formula to find where they are. There are often up to 6 that visit that client at a time so I need the search to be across B, C, D, etc. I have used XLOOKUP and it works great with just one search. For example, if I am looking for Jay and only search B, I use "=XLOOKUP(F2,B1:B4,A1:A4)". I am stuck searching more than B.

 

[Fluff]

How about

=XLOOKUP(F2:F11,TOCOL($B$2:$D$4,1),TOCOL(IF($B$2:$D$4<>"",$A$2:$A$4,1/0),2),"no match")

 

[ithoughtiknewit]

How about

=XLOOKUP(F2:F11,TOCOL($B$2:$D$4,1),TOCOL(IF($B$2:$D$4<>"",$A$2:$A$4,1/0),2),"no match")

That works, thank you!!!

 

[Fluff]

You're welcome & thanks for the feedback.