Forum Discussion
Find a word within a list and return a related number from another Cell.
I have list such as the one below, and I need to find a word within that list and once the formula finds that it will return a number from an adjacent cell. I need to be able to enter the word husky and the formula will return the numbers in column 2 (123) or say enter tabby cat and the formula would return the number 897. Is there a simple function such as =vlookup that would work? I've tried Find, Match, vLookup but so far I can't get anything to work as needed. Any help would be appreciated. Thank you.
Column 1 Column 2
brown husky 123
black retriever. 567
black tabby cat 897
persian cat 989
German shepherd 349
Let's say the phrases are in A2:A50 and the corresponding numbers in B2:B50.
Enter a word such as Husky in D2.
Enter the following formula in E2:
=XLOOKUP("*"&D2&"*", $A$2:$A$50, $B$2:$B$50, "", 2)
or
=IFERROR(VLOOKUP("*"&D2&"*", $A$2:$B$50, 2, FALSE), "")
2 Replies
Let's say the phrases are in A2:A50 and the corresponding numbers in B2:B50.
Enter a word such as Husky in D2.
Enter the following formula in E2:
=XLOOKUP("*"&D2&"*", $A$2:$A$50, $B$2:$B$50, "", 2)
or
=IFERROR(VLOOKUP("*"&D2&"*", $A$2:$B$50, 2, FALSE), "")
- Thank you for your help Hans, that worked great. I need to study your formula. I do not used Excel on a regular basis, but when I do =vlookup always comes in handy but I can see, I only have a very basic understanding of the function.
Thank you again,
Jeff
