strip based on a range.

[topi1]

Book1
	A	B	C	D	E
1	Toyota Highlander is popular	Toyota	Highlander is poular		Mazda
2	General Knowledge	General	Knowledge		Honda
3	Mercedes Benz	Mercedes Benz			Toyota
4					Ford
5					General Motors
Sheet3

Hi, In the above example, I have data in the Columns A and E. Is it possible to have formula in B1 and C1 to see if the first word in A1 matches with any word in the column E, and if it does, put that word in B1 and the rest of A1 string in C1. If there is no match in the column E, then B1=A1 and C1 is blank. As in the example.
Thank you in advance.

 

[topi1]

Intermittent gaps in column A shouldn't cause a zero unless you also have intermittent gaps in the column E data. Anyway, glad you got it sorted. This would be my small change to deal with that.

24 01 03.xlsm
	A	B	C	D	E
1	Variables	Formula			Range
2	Today is Thursday	THURSDAY			SUNDAY
3	Tomorrow is Friday				MONDAY
4					TUESDAY
5	Yesterday was Wednesday, a hump day	WEDNESDAY, A HUMP DAY			WEDNESDAY, A HUMP DAY
6
7					THURSDAY
Extract Text

Cell Formulas
Range	Formula
B2:B5	B2	=IFERROR(LOOKUP(9.99E+307,SEARCH(" "&$E$2:$E$7&" "," "&A2&" "),$E$2:$E$7)&"","")

Very nice. Works like a charm. Thanks.

 

[topi1]

@Peter_SSs in the above example, is it possible to querry more than one column? In other words, what if we want to lookup in the columns, E,F,G and so on instead of column E only. Thank you.

 

[Peter_SSs]

I think that would be a more difficult task with your excel version, but to consider it fully, could we have some new sample data and expected results with XL2BB?

 

[topi1]

Will do. I am wondering if stacking all subsequent columns as one column in column E will do the trick.