Searching text to see if it contains a value then returning a value

[levy77]

If cell A1 contains information from and cell in column C return value in column D at the same row as information.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Woolworths W7001 Sandy Bay[/TD]
[TD]2004[/TD]
[TD][/TD]
[TD]United[/TD]
[TD]2001[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Dr Jay[/TD]
[TD]2002[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Sandy Bay Newsagent[/TD]
[TD]2003[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]W7001[/TD]
[TD]2004[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Coles[/TD]
[TD]2005[/TD]
[/TR]
</tbody>[/TABLE]

Thanks for your help!

 

[levy77]

Is there a formula for this?

 

[DanteAmor]

The example is not clear, where do you want the formula?
You could explain again what you have, what data you want to find, where you want to find it and what result you want.

 

[DanteAmor]

If formula goes in cell D4 then
=vlookup("*"&C4&"*",A1:B1,2,0)

If in B1 then array formula
{=index(D1:D5,MAX((--ISNUMBER(FIND(C1:C5,A1)))*ROW(D1:D5))))}

To accept press Shift + Control + Enter

 

[Marcelo Branco]

Maybe...

B1
=IFERROR(LOOKUP(9.99E+307,SEARCH(C1:C5,A1),D1:D5),"Not found")

M.

 

[levy77]

Thanks both Marcelo and Dante.

Just to clarify, I would like the formula to be in cell B1. I want to search A1 for any matches outlined in column C. If there is a match of some sort it would return the correlating cell (same row as match) in D of which there was a match in column C. This value would be shown in cell B1.

So for example with the above table, "W7001" is contained in "Woolworths W7001 Sandy Bay" so "2004" is returned to B1.

I have attached a spreadsheet to give you an example of what I'm trying to achieve.

Thanks for your help.

https://drive.google.com/open?id=1Lhs3zZHtmY8XAO4swbqKf6HSH7oGl1hv

 

[DanteAmor]

Try second formula in post#4

 

[levy77]

This works for when there is only one row of data in column A. However when I add a list of items in column A, the formula finds the first one correctly and then just drags that value down, it doesn't seem to want to search all the rows in column A. Any idea why?

 

[Marcelo Branco]

Try

B1 copied down
=IFERROR(LOOKUP(9.99E+307,SEARCH(C$1:C$5,A1),D$1:D$5),"Not found")

M.