Forum Discussion
Solved
How can I use XLOOKUP to find the cell with a contains?
I have a table that has a cell that concatenates groups. This cell may have multiple values and they're not always in the same order due to the program that spits them out.
| Name | Group | (desired formula) |
| ex1 | ABC | ABC |
| ex2 | DEF-1, DEF | DEF |
| ex3 | DEF, DEF-1 | DEF |
| ex4 | GHI-2 , GHI-1, GHI | GHI |
| ex5 | JKL | JKL |
The table above has two columns of data that I have and the third column is what I'm trying to get.
I have another table for reference that simply has all the actual three letter combinations at a high level. I'm not concerned about the -1 or -2 sub groups. I simply need the three letter group that it belongs to.
reference table (Groups)
Group |
ABC |
| DEF |
| GHI |
| JKL |
How would I write the formula into C2 in the first table for example to get those desired three letter high level groups?
=XLOOKUP(B2,Groups!$A$2:$A$5,Groups!$A$2:$A$5)
The above gets me a match if the cell perfectly matches but I'm struggling to figure out how to make it search the text in B2 for a match from the table of Groups.
=INDEX($G$3:$G$6,XMATCH(TRUE,ISNUMBER(SEARCH($G$3:$G$6,B2))))
This formula works in my Excel for the web sheet.
=INDEX($G$3:$G$6,XMATCH(TRUE,ISNUMBER(SEARCH($G$3:$G$6,B2))))
This formula works in my Excel for the web sheet.
- That's exactly what I needed! I figured the answer was going to use something other than XLOOKUP. I'll keep INDEX and XMATCH in my toolbelt from now on.
Thank you
