Combining XLookup and if contains statements between 2 worksheets

Copper Contributor

I am needing help with pulling data between 2 worksheets within the same workbook.  The Username is listed on 2 worksheets, however, I am needing to pull data from worksheet 2 to worksheet 1 if the cell contains a partial text for that specific username.

 

I was thinking I could combine Xlookup with a IF contains formula but I am not having any luck...

1 Reply

@Carolann_Roedl 

=XLOOKUP(TRUE,ISNUMBER(SEARCH(A2,$H$2:$H$14)),$I$2:$M$14)

 

Does this return the intended result? For visualisation i've made an example in one sheet but the formula can be adapted to work between worksheets as well.

xlookup.png