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.

take a look at the FIND or SEARCH function - they can look for specific substrings that you specify, and return a number if the substring is in the target field. If it returns an error, it means that the functions don't find what you have specify. Combine the find/search functions and an xlookup — bdpolinsky
– bdpolinsky, Commented Jun 18, 2024 at 18:06

Mayukh Bhattacharya · Accepted Answer · 2024-06-18 18:31:41Z

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")

edited Jun 18, 2024 at 18:31

answered Jun 18, 2024 at 18:22

Mayukh Bhattacharya

29.1k9 gold badges31 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

KRD Over a year ago

Thank you for responding Mayukh! I do not have structured references (I was just showing table 1, etc. for illustration purposes). I tried your first two options with Filter and Xlookup, but the result is #Spill. I think it is having issues because my "look up list" i.e., the equivalent of Table 2, does not match the length/size of Table 1. Any suggestions on how to fix that? Also, in that equation, in Cell B2 in your example above, why is it referencing A2:A4? Wouldn't we want it to only reference the cell A2? And then in cell B3, it would reference A3?

Mayukh Bhattacharya Over a year ago

@KRD Structured References means Tables and that is the reason why you are getting #SPILL error. First two formulas are using LAMBDA() helper functions which will not work with tables, and hence why you are getting #SPILL error. Use the last two methods. There shouldnt be any problem arising!

KRD Over a year ago

Hi - thank you so much! The first xlookup+FIND worked perfectly. Thank you!

Mayukh Bhattacharya Over a year ago

@KRD Thank you very much. Thanks for sharing the feedback, glad to help!

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related