Matching a partial string to a list of longer strings

[cyburban]

Hello,

I am trying to match a string to another longer string. The string I am using has at least some overlap to the table I am searching for results in. Sometimes the string I am searching for is in the 2nd half of the string being searched for

This is what I am searching. Possible search values are "Direct", "Direct Drive", "Belt", "Belt Drive". I have tried using xlookup with the following formula. =XLOOKUP(C2,Table516[Fan Type],Table516[Fan Type],C2,1) Basically I am trying to match Direct to "Direct Drive" and Belt Drive or Belt to "Pulleys / Belts". It seems like excel might not be capable of this type of approximate matching? Would I be better off typing a nested IF statement formula since the number of cases I have is somewhat limited? I am dealing with human input so there is a chance stuff isn't inputted exactly the same each time. Any suggestions for how to get this working would be greatly appreciated.

 

[duggie33]

Try this for a couple options...

Book1
	A	B	C	D
55	larger string	small string	Location	T/F
56	your belt is on backwards	belt	6	TRUE
Sheet1

Cell Formulas
Range	Formula
C56	C56	=SEARCH(B56,A56,1)
D56	D56	=ISNUMBER(SEARCH(B56,A56,1))

Hope that helps,

Doug