This seems like simple concept at least to my limited excel knowledge, but I'm unable to find a formula that does the following.
I have two columns of text, column A & column B. I want to search column B for a the partial text of a person's name (the first match in the list is fine) and return the data from the same row in Column A (phone number (entered as text)).
Ideally this would work whether the list to search is in any column (or is easily adjusted to return any column), but the cell to match will always be in Column A.
Example:
Search column B for ""Tom"", return text from Column A that corresponds (in this example the value in A6, "555-555-5559")
This is what I think should be working.
=VLOOKUP("*"&TOM&"*",DATASHEETWITHARRAY'!A2:B10,1,1)1 Answer
You can use XLOOKUP instead. Taking your example, you could do this formula:
=XLOOKUP( "TOM*", DATASHEETWITHARRAY!$B$2:$B$10, DATASHEETWITHARRAY!$A$2:$A$10,,2 )If you want to make it more general, where it looks in a cell to match against the lookup column, you can do it like this:
=XLOOKUP( D2&"*", DATASHEETWITHARRAY!$B$2:$B$10, DATASHEETWITHARRAY!$A$2:$A$10,,2 )Where D2 is the cell that you are looking up, DATASHEETWITHARRAY!$B$2:$B$10 contains your names, and DATASHEETWITHARRAY!$A$2:$A$10 contains your phone numbers.
XLOOKUP supports wildcards. If you put a * at the end of the text, it will look for anything that starts with TOM. Put it at the beginning (*TOM) and it will look for anything that ends with TOM. You can also use ? to make a single character a wildcard - for example, your T?M would find TOM or TIM (or TAM or T4M ...).
Just remember to set the search mode in XLOOKUP to 2, which is the 5th variable. The 4th variable shows what you want to return when there is no match. If you leave it blank, it will return an N/A error.
