Lookup from a cell any word in a column and return value next to it...

[James Burger]

Hi Everyone,

Table1:
[TABLE="width: 500"]
<tbody>[TR]
[TD]UK[/TD]
[TD]Europe[/TD]
[/TR]
[TR]
[TD]France[/TD]
[TD]Europe[/TD]
[/TR]
[TR]
[TD]Germany[/TD]
[TD]Europe[/TD]
[/TR]
[TR]
[TD]India[/TD]
[TD]Asia[/TD]
[/TR]
[TR]
[TD]Christmas Island[/TD]
[TD]Asia[/TD]
[/TR]
[TR]
[TD]South Africa[/TD]
[TD]Africa[/TD]
[/TR]
</tbody>[/TABLE]

I have a question please:

[TABLE="width: 500"]
<tbody>[TR]
[TD]543 Germany[/TD]
[/TR]
[TR]
[TD]09235035Germany333[/TD]
[/TR]
[TR]
[TD]South Africa fjjdogjg sogj osidj[/TD]
[/TR]
[TR]
[TD]UK 034jhb 4ff[/TD]
[/TR]
[TR]
[TD]345Christmas Island3 k3k[/TD]
[/TR]
</tbody>[/TABLE]

I have the above cells in column A - I would like a formula that looks up value in cell A1[543 Germany] and says if it contains any words that is in the first column of "Table 1" then return the value next to it if it finds it... otherwise say "NO FIND" so the complete table will show:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Value:[/TD]
[TD]Formula result:[/TD]
[/TR]
[TR]
[TD]543 Germany[/TD]
[TD]Europe[/TD]
[/TR]
[TR]
[TD]09235035Germany333[/TD]
[TD]Europe[/TD]
[/TR]
[TR]
[TD]South Africa fjjdogjg sogj osidj[/TD]
[TD]Africa[/TD]
[/TR]
[TR]
[TD]UK 034jhb 4ff[/TD]
[TD]Europe[/TD]
[/TR]
[TR]
[TD]345Christmas Island3 k3k[/TD]
[TD]Asia[/TD]
[/TR]
[TR]
[TD]dfhrrhrhrhrhrdh[/TD]
[TD]NO FIND[/TD]
[/TR]
</tbody>[/TABLE]

Is this possible please? what formula do I put under the column formula result for it to return the correct values as above?

Appreciate any feedback on this

hope this makes sense

Thanks

 

[shg]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][td="bgcolor:#C0C0C0"]

F​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]UK[/td][td]Europe[/td][td][/td][td]543 Germany[/td][td="bgcolor:#CCFFCC"]Europe[/td][td="bgcolor:#CCFFCC"]E2: =LOOKUP(2, 1/ISNUMBER(SEARCH($A$2:$A$7, D2)), $B$2:$B$7)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]France[/td][td]Europe[/td][td][/td][td]09235035Germany333[/td][td="bgcolor:#CCFFCC"]Europe[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]Germany[/td][td]Europe[/td][td][/td][td]South Africa fjjdogjg sogj osidj[/td][td="bgcolor:#CCFFCC"]Africa[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]India[/td][td]Asia[/td][td][/td][td]UK 034jhb 4ff[/td][td="bgcolor:#CCFFCC"]Europe[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]Christmas Island[/td][td]Asia[/td][td][/td][td]345Christmas Island3 k3k[/td][td="bgcolor:#CCFFCC"]Asia[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]South Africa[/td][td]Africa[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

 

[jtakw]

Hi,

Don't think the ISNUMBER check is needed:

Book1
	A	B	C	D	E
1	Table1:			Value:	Formula result:
2	UK	Europe		543 Germany	Europe
3	France	Europe		09235035Germany333	Europe
4	Germany	Europe		South Africa fjjdogjg sogj osidj	Africa
5	India	Asia		UK 034jhb 4ff	Europe
6	Christmas Island	Asia		345Christmas Island3 k3k	Asia
7	South Africa	Africa		dfhrrhrhrhrhrdh	NO FIND
Sheet160

Cell Formulas
Range	Formula
E2	=IFERROR(LOOKUP(2,1/SEARCH(A$2:A$7,D2),B$2:B$7),"NO FIND")

E2 formula copied down.

 

[shg]

It makes it choose the last match among multiple. Without it, there is a dependency on where the word appears in the string.

 

[jtakw]

I've used this formula without ISNUMBER on many occasions, it will return the last match in the range...

Maybe I'm missing something? see my sample in Post #3

Also, the LOOKUP will ignore #VALUE errors produced by the SEARCH, so it will return the last match, if there is one. (the ISNUMBER just converts the #VALUE errors and the match to TRUE/FALSE),

 

[James Burger]

Well thank you two very much I really appreciate it!

 

[shg]

You are indeed correct.

 

[jtakw]

You're welcome James.

Thank you, shg.