Can we match a string with another string?

GN0001 · May 7, 2014

Hello All,

I have two lists, I need to match these two lists through their texts? is this possible?
If one part of text is found in another text , we need to take these two cells as matching values.

The reason for doing this is the first list is mapping to some ids and the second list is mapped to a different id. I have to find for the first id from first list the matching id from the second list.

As long as some part of the text is found in second list we can consider the ids as match.

For Example if the first list has the word bank and the second list has bank of Commercial, these two cells should be considered as match. The texts for these two cells can be anything and any length but as long as the first and second has only on word common, for my project, they are considered match.

Is there any way to take care of this task without using VBA code? Please advise.

Regards,
GN0001

Debraj · May 7, 2014

Hi GN0001

Its almost 500 post. still you are not helping us.. to help you..

Vishal Srivastava · May 7, 2014

Dear GN0001,
Suppose you have first list in Column 'A' and Second is in 'B': You can use following formula in Column 'C' to lookup for matching text:
{=INDEX(B1:B9,MATCH("*"&A1:A9&"*",B1:B9,0))}

Thanks & Regards,
CMA Vishal Srivastava

GN0001 · May 8, 2014

GN0001 said:
Hello All,

I have two lists, I need to match these two lists through their texts? is this possible?
If one part of text is found in another text , we need to take these two cells as matching values.

The reason for doing this is the first list is mapping to some ids and the second list is mapped to a different id. I have to find for the first id from first list the matching id from the second list.

As long as some part of the text is found in second list we can consider the ids as match.

For Example if the first list has the word bank and the second list has bank of Commercial, these two cells should be considered as match. The texts for these two cells can be anything and any length but as long as the first and second has only on word common, for my project, they are considered match.

Is there any way to take care of this task without using VBA code? Please advise.

Regards,
GN0001

Debraj said:
Hi GN0001

Its almost 500 post. still you are not helping us.. to help you..

Hello Debraj,
I have thought times and time that how I can pay this goodness back. What team members have been doing here is great and I can't find any word to appreciate all this. But my knowledge is not that much to help others, I don't want to help a member in an inadequate way and misguide members.
This is not the only forum I go, I go to different forums to get my problems resolved and usually people are great. Eventually and hopefully, I will come to a point that I can help others but trust me, I am not in that level yet.
Thanks,
GN0001

GN0001 · May 8, 2014

Vishal Srivastava said:
Dear GN0001,
Suppose you have first list in Column 'A' and Second is in 'B': You can use following formula in Column 'C' to lookup for matching text:
{=INDEX(B1:B9,MATCH("*"&A1:A9&"*",B1:B9,0))}

Thanks & Regards,
CMA Vishal Srivastava

Hello Vishal,
This worked,however I couldn't find more than 5 matches, Do you think of some other way?
Also, I didn't copy and paste the function, the values are displayed in the range. Can array functions paste able? Or do they get the values to be displayed on the range? Please advise me.

Thank you for your function and wish you the best of all.
GN0001

Debraj · May 8, 2014

GN0001 said:
Hello Debraj,
I have thought times and time that how I can pay this goodness back.

Hi Guity..
If you can help by solving issue, that will be great.. but right now.. I am just asking for sample file.. which will help us lot..

Vishal Srivastava · May 8, 2014

GN0001 said:
Hello Vishal,
This worked,however I couldn't find more than 5 matches, Do you think of some other way?
Also, I didn't copy and paste the function, the values are displayed in the range. Can array functions paste able? Or do they get the values to be displayed on the range? Please advise me.

Thank you for your function and wish you the best of all.
GN0001

Dear GN0001,
Try this,

=LOOKUP(2,1/SEARCH("*"&A2&"*",B:B),B:B)

Hope it will help you!

CMA Vishal Srivastava

GN0001 · May 9, 2014

Debraj said:
Hi Guity..
If you can help by solving issue, that will be great.. but right now.. I am just asking for sample file.. which will help us lot..

Hello Debraj,
I have posted the file.
Thanks,
GN0001

Debraj · May 9, 2014

Hi GN,

try this one in Sheet 2, B2..

=LOOKUP(2,1/ISNUMBER(SEARCH(A2,List_A)),List_A)

Where List_A =Sheet1!$A$1:$A$7

Which is ..
=LOOKUP(2,1/ISNUMBER(SEARCH(A2,Sheet1!$A$2:$A$7)),Sheet1!$A$2:$A$7)

GN0001 · May 9, 2014

Vishal Srivastava said:
Dear GN0001,
Try this,

=LOOKUP(2,1/SEARCH("*"&A2&"*",B:B),B:B)

Hope it will help you!

CMA Vishal Srivastava

-------------------------------------------------
Hello Vishal,
Thank you for the great answer.
But a couple of things:
1-I tried search function, it always bring back 1, why is the reason?
2-Lookup(2,1), how does it know to bring back Wells Fargo (the correct texts)? What 2 and 1 refer to?
I have uploaded the file
3-I read LookUp function, it says it is provided for back up compatibility.
I appreciate your response.
GN0001

GN0001 · May 9, 2014

Debraj said:
Hi GN,

try this one in Sheet 2, B2..

=LOOKUP(2,1/ISNUMBER(SEARCH(A2,List_A)),List_A)

Where List_A =Sheet1!$A$1:$A$7

Which is ..
=LOOKUP(2,1/ISNUMBER(SEARCH(A2,Sheet1!$A$2:$A$7)),Sheet1!$A$2:$A$7)

------------------------
Hello Debraj,
The function posted by Vishal worked. I can't understand some part of that? What does 2 and 1 refer to in (2,1/ISNUMBER(SEA? Because once I scan the function the function comes to lookup(2,1).

Also in description of the function says that LookUp provides backward compatibility. what does it mean?

Thank you for the response.
You wont' believe how much I appreciate your response and Vishal's response.
GN0001

Vishal Srivastava · May 9, 2014

GN0001 said:
-------------------------------------------------
Hello Vishal,
Thank you for the great answer.
But a couple of things:
1-I tried search function, it always bring back 1, why is the reason?
2-Lookup(2,1), how does it know to bring back Wells Fargo (the correct texts)? What 2 and 1 refer to?
I have uploaded the file
3-I read LookUp function, it says it is provided for back up compatibility.
I appreciate your response.
GN0001

Dear GN0001,

At First understand how lookup function works:

1) LOOKUP(LookupValue,LookupVector,ResultVector)

LOOKUP(LookupValue,Array)

Where LookupVector is an Array object.

It can be noted that LOOKUP function is capable of returning computed arrays.

2) LOOKUP(n,Ref)

will all return the last numerical value from Ref when n is a number that cannot occur in Ref.

3) Now come to our formula:

=LOOKUP(2,1/SEARCH("*"&A2&"*",B:B),B:B)

Looking up 2 in that will return the position of the last "1". Here 2 refers to "BigNumber". The logic behind 2 is that 2 is a BigNumber with respect to 1/SEARCH("*"&A2&"*",B:B) and 1 divided by any number cannot be equal to or greater than 2.
Instead we could avoid division through using Big number. such as 9.99999999999999E+307 or 32768 (Int limit), etc.

e.g:
=LOOKUP(9.99999999999999E+307,SEARCH("*"&A2&"*",B:B),B:B)
or
=LOOKUP(32768,SEARCH("*"&A2&"*",B:B),B:B)

Hope you understand better!

Thanks & Regards,
CMA Vishal Srivastava

GN0001 · May 10, 2014

Hello Vishal,
My world will be changed if I understand this post.
First I need to understand the LookUp function:

is this our array?

Wells Fargo
Bank of Commercial
SCC2 AB
1 Project
Glassdoor

are these our lookup values?
Wells
Bank
AB
Project
Client Site

------------------------------------------------------------------------
I have been reading about lookup function on the techonthenet

It says, the lookup function searches for the values in the first row and column of the array and returns
the corresponding values in the last row or column of the array?

In array such as

Wells Fargo
Bank of Commercial
SCC2 AB
1 Project
Glassdoor

what would be first row and column of the array?

Can you please give me an example of an array with Rows and Columns?

------------------------------------------------------------------------------
What does this lookup function return?
=LOOKUP(C2,{0,"A","B","C","D","E","F","G","H","I","K","X","Z"}, {0,"1","2","3","4","5","6","7","8","9","10","12","1"})

------------------------------------------------------------------------
3) Now come to our formula:
=LOOKUP(2,1/SEARCH("*"&A2&"*",B:B),B:B)
Looking up 2 in that will return the position of the last "1"

in an array such as:
Wells Fargo
Bank of Commercial
SCC2 AB
1 Project
Glassdoor
Where is 2? That might sound funny. I can think of what you said:"will all return the last numerical value from Ref when n is a number that cannot occur in Ref."

We are looking for 2, because we can't find it, it will return the last numberical value from ref, when 2 is not in the array at all and 1 is the location when the second string happens in the first string. Right?

I appricate your attempt on this.
GN0001

Vishal Srivastava · May 12, 2014

Dear GN0001,

Firstly, You should understand that the lookup functions ignore error values.

For clear understanding:
I am moving Sheet1--> Well Fargo from 2nd Row to 6th Row.

Here is our formula:

=LOOKUP(2,1/SEARCH("*"&A2&"*",Sheet1!$A$2:$A$7),Sheet1!$A$2:$A$7)

is structured as:

LOOKUP(LookupValue,LookupVector,ResultVector)

where a vector can be an array like {1,2,9,5,7,6} or any range like A2:A7

Here,

"1/SEARCH("*"&A2&"*",Sheet1!$A$2:$A$7)" is an array object.

Now evaluate:

SEARCH("*"&A2&"*",Sheet1!$A$2:$A$7)

It has logical values like:

{#VALUE!;#VALUE!;#VALUE!;#VALUE!;1;#VALUE!}

then,

1/{#VALUE!;#VALUE!;#VALUE!;#VALUE!;1;#VALUE!}

==> {#VALUE!;#VALUE!;#VALUE!;#VALUE!;1;#VALUE!}

it returns the error code for division by #VALUE! when the value in parenthesis is false (a #VALUE!), and it returns a 1 for 1/1 when true.

Since the last numerical value is the 5th item in the foregoing array, LOOKUP will retrieve the 5th item from Sheet1!$A$2:$A$7, the ResultVector.

Let's take up our our formula:

=LOOKUP(2,1/SEARCH("*"&A2&"*",Sheet1!$A$2:$A$7),Sheet1!$A$2:$A$7)

=LOOKUP(2,1/SEARCH("*"&A2&"*",{"Glassdoor";"Bank of Commercial";"SCC2 AB";"1 Project";"Wells Fargo";"implementation"}),{"Glassdoor";"Bank of Commercial";"SCC2 AB";"1 Project";"Wells Fargo";"implementation"})

=LOOKUP(2,1/{#VALUE!;#VALUE!;#VALUE!;#VALUE!;1;#VALUE!},{"Glassdoor";"Bank of Commercial";"SCC2 AB";"1 Project";"Wells Fargo";"implementation"})

=LOOKUP(2,{#VALUE!;#VALUE!;#VALUE!;#VALUE!;1;#VALUE!},{"Glassdoor";"Bank of Commercial";"SCC2 AB";"1 Project";"Wells Fargo";"implementation"})

Pls note: "Looking up 2 in that will return the position of the last "1". 2 could be any number which represents Big Number and it must be greater than 1" .
Now it can be seen, the last numerical value i.e.1 is the 5th item. As it so happens, the last numerical value here is also the only numerical value. Hereafter LOOKUP proceeds to fetch the 5th item from

{"Glassdoor";"Bank of Commercial";"SCC2 AB";"1 Project";"Wells Fargo";"implementation"}

Which is "Wells Fargo".

Hope you understand better.

Thanks & Regards,
CMA Vishal Srivastava

GN0001 · May 19, 2014

Vishal Srivastava said:
Dear GN0001,

Firstly, You should understand that the lookup functions ignore error values.

For clear understanding:
I am moving Sheet1--> Well Fargo from 2nd Row to 6th Row.

Here is our formula:

=LOOKUP(2,1/SEARCH("*"&A2&"*",Sheet1!$A$2:$A$7),Sheet1!$A$2:$A$7)

is structured as:

LOOKUP(LookupValue,LookupVector,ResultVector)

where a vector can be an array like {1,2,9,5,7,6} or any range like A2:A7

Here,

"1/SEARCH("*"&A2&"*",Sheet1!$A$2:$A$7)" is an array object.

Now evaluate:

SEARCH("*"&A2&"*",Sheet1!$A$2:$A$7)

It has logical values like:

{#VALUE!;#VALUE!;#VALUE!;#VALUE!;1;#VALUE!}

then,

1/{#VALUE!;#VALUE!;#VALUE!;#VALUE!;1;#VALUE!}

==> {#VALUE!;#VALUE!;#VALUE!;#VALUE!;1;#VALUE!}

it returns the error code for division by #VALUE! when the value in parenthesis is false (a #VALUE!), and it returns a 1 for 1/1 when true.

Since the last numerical value is the 5th item in the foregoing array, LOOKUP will retrieve the 5th item from Sheet1!$A$2:$A$7, the ResultVector.

Let's take up our our formula:

=LOOKUP(2,1/SEARCH("*"&A2&"*",Sheet1!$A$2:$A$7),Sheet1!$A$2:$A$7)

=LOOKUP(2,1/SEARCH("*"&A2&"*",{"Glassdoor";"Bank of Commercial";"SCC2 AB";"1 Project";"Wells Fargo";"implementation"}),{"Glassdoor";"Bank of Commercial";"SCC2 AB";"1 Project";"Wells Fargo";"implementation"})

=LOOKUP(2,1/{#VALUE!;#VALUE!;#VALUE!;#VALUE!;1;#VALUE!},{"Glassdoor";"Bank of Commercial";"SCC2 AB";"1 Project";"Wells Fargo";"implementation"})

=LOOKUP(2,{#VALUE!;#VALUE!;#VALUE!;#VALUE!;1;#VALUE!},{"Glassdoor";"Bank of Commercial";"SCC2 AB";"1 Project";"Wells Fargo";"implementation"})

Pls note: "Looking up 2 in that will return the position of the last "1". 2 could be any number which represents Big Number and it must be greater than 1" .
Now it can be seen, the last numerical value i.e.1 is the 5th item. As it so happens, the last numerical value here is also the only numerical value. Hereafter LOOKUP proceeds to fetch the 5th item from

{"Glassdoor";"Bank of Commercial";"SCC2 AB";"1 Project";"Wells Fargo";"implementation"}

Which is "Wells Fargo".

Hope you understand better.

Thanks & Regards,
CMA Vishal Srivastava

Hello Vishal,
I understand this better now, certainly.

You have noted:

Pls note: "Looking up 2 in that will return the position of the last "1".

does this refer to this part?
LOOKUP(n,Ref)
will all return the last numerical value from Ref when n is a number that cannot occur in Ref.

Regards, GN0001

GN0001 · May 19, 2014

Debraj said:
Hi GN,

try this one in Sheet 2, B2..

=LOOKUP(2,1/ISNUMBER(SEARCH(A2,List_A)),List_A)

Where List_A =Sheet1!$A$1:$A$7

Which is ..
=LOOKUP(2,1/ISNUMBER(SEARCH(A2,Sheet1!$A$2:$A$7)),Sheet1!$A$2:$A$7)

Dear Debraj,
Would you be so nice as to tell me, what is the result of this function?
What does this lookup function return?
=LOOKUP(C2,{0,"A","B","C","D","E","F","G","H","I","K","X","Z"}, {0,"1","2","3","4","5","6","7","8","9","10","12","1"})?

Also, the function provides backward compatability?

Thanks,
GN0001

NARAYANK991 · May 20, 2014

Hi ,

The LOOKUP function that you have posted does the same action as both of the following :

=INDEX( {0,"1","2","3","4","5","6","7","8","9","10","12","1"},MATCH(C2,{0,"A","B","C","D","E","F","G","H","I","K","X","Z"}))

where the MATCH function uses an approximate match , not an exact one.

=HLOOKUP(C2,{0,"A","B","C","D","E","F","G","H","I","K","X","Z";0,"1","2","3","4","5","6","7","8","9","10","12","1"},2)

Narayan

Vishal Srivastava · May 20, 2014

GN0001 said:
Hello Vishal,
I understand this better now, certainly.

You have noted:

Pls note: "Looking up 2 in that will return the position of the last "1".

does this refer to this part?
LOOKUP(n,Ref)
will all return the last numerical value from Ref when n is a number that cannot occur in Ref.

Regards, GN0001

Exactly You have got my point now!

Suppose our logical values are as under:

{1;#VALUE!;1;#VALUE!;#VALUE!}

Here, It will look for position of last 1 which is 3rd item not 1st item.

Thanks & Regards,
CMA Vishal Srivastava

GN0001 · Jun 8, 2014

Vishal,
Thank you for all these answers. I understand it now, but I need to practice more to be able to apply it in other places, good luck on your way, GN0001

Can we match a string with another string?

Member

Excel Ninja

Member

Member

Member

Excel Ninja

Member

Member

Attachments

Excel Ninja

Member

Attachments

Member

Member

Member

Member

Member

Member

Excel Ninja

Member

Member