ISNumber / Search

[kyleball]

Hi All,

I have tried to search for this and because I'm trying to be specific the results have not been great, although I'm sure it's there somewhere (just not searching the right things).

What I want to do - if the name in column A is found in column D, I want it to not only let me know but pull in what was found. Data below:


[TABLE="width: 412"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD][/TD]
[TD]Column D[/TD]
[/TR]
[TR]
[TD]olita[/TD]
[TD][/TD]
[TD][/TD]
[TD]olita ( accountant)[/TD]
[/TR]
[TR]
[TD]zara[/TD]
[TD][/TD]
[TD][/TD]
[TD]ella[/TD]
[/TR]
[TR]
[TD]kyle[/TD]
[TD][/TD]
[TD][/TD]
[TD]martin Williams [/TD]
[/TR]
[TR]
[TD]martin[/TD]
[TD][/TD]
[TD][/TD]
[TD]baby zara[/TD]
[/TR]
[TR]
[TD]ella[/TD]
[TD][/TD]
[TD][/TD]
[TD]KYLE

[/TD]
[/TR]
</tbody>[/TABLE]



So next to zara I would want it to return baby zara.

The point I go to was this:

=IF(ISNUMBER(SEARCH(A2,$D$2:$D$6)),$D$2:$D$6,"No")

Which only returned olita ( accountant), and none of the others.

Any help greatly appreciated

Thanks,

Kyle

 

[philwojo]

There might be a better way to do this, but try this.

=INDEX(D:D,MATCH("*"&A1&"*",D:D,0),1)

 

[Aladin Akyurek]

=VLOOKUP("*"&A2&"*",D:D,1,0)

would also work as intended.

 

[kyleball]

There might be a better way to do this, but try this.

=INDEX(D:D,MATCH("*"&A1&"*",D:D,0),1)

Thank you - that worked perfectly!

 

[kyleball]

=VLOOKUP("*"&A2&"*",D:D,1,0)

would also work as intended.

Again, that worked too. So thank you for that. Just in case someone needs to try either.