If cell contains text from list... My formula returns "0"

[sakis_s]

Hi all,
I have this example:

A	B	C	D	E
TRAILING SADDLE BAG			BAG	555
			SHOE	333

My goal is to enter a formula in B1 cell to look for text inside A1 cell from list in D column, and when/if it finds the text then paste the text from E column.

So the formula i'm using for this in B1 cell is the following:

=IFERROR(LOOKUP(1;0/COUNTIF(A1;"*"&D:D&"*");E:E);"No Match")

The problem is that it returns "0" as a result.
Anyone knows what i'm doing wrong?

Thank you in advance for your help, i really appreciate your time.

 

[jorismoerings]

Hi,

try This:

Book1
	A	B	C	D	E
1	TRAILING SADDLE BAG	555		BAG	555
2				SHOE	333
Sheet1

Cell Formulas
Range	Formula
B1	B1	=SUMPRODUCT(--ISNUMBER(SEARCH($D$1:$D$2,A1))*E1:E2)

 

[sakis_s]

Hi,

try This:

Book1
	A	B	C	D	E
1	TRAILING SADDLE BAG	555		BAG	555
2				SHOE	333
Sheet1

Cell Formulas
Range	Formula
B1	B1	=SUMPRODUCT(--ISNUMBER(SEARCH($D$1:$D$2,A1))*E1:E2)

Hi and thank you very much for your reply.

Unfortunately it doesn't work either. I get #VALUE! error.

 

[jorismoerings]

Hi and thank you very much for your reply.

Unfortunately it doesn't work either. I get #VALUE! error.

Hi,

Did you test it with the given data from your example or did you test it on a more extensive set of data?
Are you able to show #VALUE error using XL2BB.

XL2BB is an add in designed for MrExcel to be able to copy Excel data and formula's onto the forum.
More info on how to get and install it, to be found here

 

[Peter_SSs]

Some other clarification too please.

With your sample data in columns D:E, what would you want returned if A1 was "TRAILING SADDLE BAGGAGE"?
That is, are looking for simple text or are you looking for whole words?

Is it possible that more than one item from column D might appear in the column A text?
If so, explain what result(s) you would want?