Excel search for certain text within cell and return vlookup from a range

Question

In Table 1 I have a column of Names in one tab (column H) and would like to bring in the Contact information (column I) based on a list (columns A and B) in a separate tab (Table 2). Table 1 example:

Name	Contact Info
Univ of Utah Facilities	?
BMI properties	?
C/O Health HOA Management	?

List Table 2 example:

If Name contains the following text	Contact Info
Univ of Utah	[email protected]
BMI	123-456-7890
Health HOA	[email protected]

I would like my original Table 1 to look like this Table 3 example:

Name	Contact Info
Univ of Utah Facilities	[email protected]
BMI properties	123-456-7890
C/O Health HOA Management	[email protected]

The issue I'm encountering is that it's looking only for an exact match of contents of the full name cell (even when I use "X" wildcard). Is there a way to search for just specific text within the cell (e.g., BMI within a cell even though the cell says BMI Properties) and return as a vlookup or something, the value in the adjacent cell? Thank you for your help! I've searched extensively and nothing works.

Answer 1

Here is one way of achieving the desired output:

---

=MAP(A2:A4,LAMBDA(α, FILTER(E2:E4,1-ISERR(FIND(" "&D2:D4&" "," "&α&" ")))))

---

Similarly, can use XLOOKUP() as well:

=MAP(A2:A4,LAMBDA(δ,XLOOKUP(1,1-ISERR(FIND(" "&D2:D4&" "," "&δ&" ")),E2:E4)))

---

Please ensure to change the cell range and reference as per your suit.

---

Also since you mention in the OP, you are using Structured References then use it in the following way:

---

=XLOOKUP(1,
 1-ISERR(FIND(" "&Table2[If Name contains the following text]&" "," "&[@Name]&" ")),
 Table2[Contact Info])

---

If have access to REGEXTEST() can test as well, this works on my end:

=XLOOKUP(TRUE, 
 REGEXTEST(A2,"\b"&Table2[If Name contains the following text]&"\b"),
 Table2[Contact Info],"None")

---