Find cell that contains certain text and return the value of the cell

[kvazar]

I have table 1:[TABLE="width: 500"]
<tbody>[TR]
[TD]Column 1
James Phillips[/TD]
[TD]Formula column
formula to return from column 2 below (Phillips)[/TD]
[/TR]
[TR]
[TD]Lando Boris[/TD]
[TD]formula to return from column 2 below (Lando)[/TD]
[/TR]
[TR]
[TD]For Mr. B. McComb[/TD]
[TD]formula to return from column 2 below (McComb)[/TD]
[/TR]
</tbody>[/TABLE]

and table 2:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Search in this column
Phillips[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lando[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]McComb[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So. I want to return surnames only. So, I need to search table 1 column 1, if it contains ANY of the text from table 2 column 1, and if yes, then return it.
I tried ISNUMBER, doesn't help obviously, maybe INDEX with CTRL+SHIFT+ENTER ?

 

[Jon von der Heyden]

Welcome to the board!

Perhaps;

Excel 2010
	A	B
1	I have table 1:
2
3	James Phillips	Phillips
4	Lando Boris	Lando
5	For Mr. B. McComb	McComb
6
7
8	and table 2:
9	Phillips
10	Lando
11	McComb
Sheet1

Cell Formulas
Range	Formula
B3	=INDEX($A$9:$A$11,LOOKUP(10^308,MATCH(TRIM(MID(SUBSTITUTE(A3," ",REPT(" ",255)),{1,2,3,4,5}*255-254,255)),$A$9:$A$11,0)),1)

 

[kvazar]

Thank you, Jon!

Actually that did work. Though I'm not even close to understanding some of attributes of the formula. Like 10^308 or 1,2,3,4,5. Seems like a grid?

 

[Jon von der Heyden]

Hi kvazar

I can offer you a brief explanation of how this formula works.

TRIM(MID(SUBSTITUTE(A3," ",REPT(" ",255)),{1,2,3,4,5}*255-254,255))

This component yield an array of each word within A3, where the space is the separator between words. It supports up to 5 words (or names). What it does is pad the words with 255 space characters and then grab each word using MID (255 characters at a time), and then trims off the eroneous spaces. So for example for A3 it will yield an array as follows:

{"James","Phillips","","",""}

The last 3 are blank because there are only two words. I will refer to this component now as words_array.

This formula is nested within a LOOKUP formula, as follows:
LOOKUP(10^308,MATCH(words_array,$A$9:$A$11,0))

Firstly the MATCH is attempting to locate each of the words in words_array to the table in A9:A11. If a match exists then it will return the index of the match (i.e. the position of where the match was found in the range). This again will yield an array because each array item is sought in the table.

{#N/A,1,#N/A,#N/A,#N/A}

The first, third, forth and fifth items are #N/A because either the item was blank or it pertained to name not found. The second item is 1, which is the index of "Phillip" in the lookup range.

The LOOKUP is used to extrapolate the 1. A behaviour inherent to LOOKUP is to ignore errors in the lookup array, so #N/A is ignored. LOOKUP performs a binary search and therefore we can achieve a match of the last numeric item in the array by seeking the number 10^308. You need to do some research on LOOKUP and binary search to understand this properly. We will refer to the LOOKUP result as item_index.

Once the LOOKUP has achieved it's task it will have found the position of the matching name in the lookup table, so in the next step we use INDEX to locate the result from the table:
INDEX($A$9:$A$11,item_index,1)

Index scans the lookup range (A9:A11) and yields a result corresponding to the 1st row (item_index) and the 1st column (as it is a 1 column range).

Voila - you have your result!

 

[kvazar]

I actually googled binary search and 10^308 and ended up on your blog's page: Extract a number from a text string with LOOKUP Jon von der Heyden

Thank you for a very detailed and thorough answer, this really helps!

Kind Regards.

 

[barnsey]

Oh wow that's very useful information. Does it work if the cell contains digits or it works only if it's text?

 

[F1SH_T4C0]

Hi kvazar

Once the LOOKUP has achieved it's task it will have found the position of the matching name in the lookup table, so in the next step we use INDEX to locate the result from the table:
INDEX($A$9:$A$11,item_index,1)

Index scans the lookup range (A9:A11) and yields a result corresponding to the 1st row (item_index) and the 1st column (as it is a 1 column range).

Voila - you have your result!

Dear Jon,

I'm really impressed by this formula and how it works, however, I cannot get it to work with an array that goes across multiple columns (for example $A$9:$B:$11)
It always returns an error. I have tried to repeat the formula with IFERROR(original formula, original formula + new array (second column)) but this is very taxing on the CPU.

Is there a way to extend the array to include multiple columns?

Also, THANK YOU!!!

 

[p9j123]

I need this exact formula, just wanted to a bit of modification

James D-Phillips on this example I still need to capture Phillips even if i there is no space on the word or letter before it.

Welcome to the board!

Perhaps;

Excel 2010

	A	B
I have table 1:
James Phillips	Phillips
Lando Boris	Lando
For Mr. B. McComb	McComb
and table 2:
Phillips
Lando
McComb

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B3[/TH]
[TD="align: left"]=INDEX($A$9:$A$11,LOOKUP(10^308,MATCH(TRIM(MID(SUBSTITUTE(A3," ",REPT(" ",255)),{1,2,3,4,5}*255-254,255)),$A$9:$A$11,0)),1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]