Problem with Match function...

[DizzyDoodle]

I'm having two problems with the match function......

The first:

The match function is not finding every value of which I know exists in the database. I've made a small test that represents this problem below.

When using the below match function I can identify the central variable { C1 = X00807 } in only the Datapoint 2 and not Datapoint 1.

=MATCH("*"&C1&"*",I1:I9,0)

[TABLE="width: 178"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Datapoint 1 =
-------------------------------------------------------------------------------------------------------
Coal Advisory Group#X00807#Brian Ricketts#http://ec.europa.eu/transparency/re...groupID=807#Member#B#Employees/workers#Expert Group on the exchange of information on Best Available Techniques related to industrial emissions (IED Article 13 Forum)#E02611#http://ec.europa.eu/transparency/re...groupDetail.groupDetail&groupID=2611#Member#C
-------------------------------------------------------------------------------------------------------
Datapoint 2 =

-------------------------------------------------------------------------------------------------------​

[TABLE="width: 178"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Coal Advisory Group#X00807#Reinhold Elsen#http://ec.europa.eu/transparency/re...ail.groupDetail&groupID=807#Member#B#Industry

-------------------------------------------------------------------------------------------------------​

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

This is odd because one would expect match to identify the number in the array of Datapoint 1. Indeed, if I remove Datapoint 1 it does not find X00807 in Datapoint 2.

I did some testing, and it will find X00807 if I change the Datapoint to the following:
​

[/TD]
[/TR]
[TR]
[TD]Datapoint 1:

-------------------------------------------------------------------------------------------------------​

<strike style="background-attachment: scroll; background-clip: border-box; background-color: transparent; background-image: none; background-origin: padding-box; background-position-x: 0%; background-position-y: 0%; background-repeat: repeat; background-size: auto; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-size-adjust: none; font-stretch: 100%; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; line-height: normal; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; orphans: 2; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; word-spacing: 0px;"></strike>

Coal Advisory Group#X00807#Brian Ricketts#http://ec.europa.eu/transparency/re...groupID=807#Member#B#Employees/workers#Expert Group on the exchange of information on Best Available Techniques related to industrial

-------------------------------------------------------------------------------------------------------

This suggests to me that the string is too long for the match function to find the value, but why? Is there a better way I can search for it? It is far too tedious to sift through and change the data manually, as it conisists of around 11,000 rows.

This brings me onto my second question:

Forgetting the problem with uncovering all of the datapoints, for the ones that do work I can use the below formula to look through the array:
​

<strike style="background-attachment: scroll; background-clip: border-box; background-color: transparent; background-image: none; background-origin: padding-box; background-position-x: 0%; background-position-y: 0%; background-repeat: repeat; background-size: auto; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-size-adjust: none; font-stretch: 100%; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; line-height: normal; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; orphans: 2; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; word-spacing: 0px;"></strike>​

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

=SMALL(MATCH("*"&C1&"*",I1:I9,0),1)

This works fine, however, when I try to use values of n>1 it returns a #Num ! error. Why? I know the array consists of many variables, as if I delete datapoints in the array the return value changes. What am I doing wrong?

If you'd like to understand what I am trying to do:

Within large strings I need to find the ones that contain a particular substring corresponding to given codes, such as the one I used in the example (X00807), and then list them so that I know which rows contain a string with that code inside.

Any help would be greatly appreciated.
​

 

[RasGhul]

Can you post a sample of the rows you want to list?

Is the goal to return;
Coal Advisory Group#X00807#Brian
Coal Advisory Group#X00807#Reinhold
etc?

 

[StephenCrump]

Welcome to the Forum!

This suggests to me that the string is too long for the match function to find the value ...

You're right. Match with wildcards won't work on strings >255 characters.

An alternative would be to use: =MATCH(TRUE,ISNUMBER(SEARCH(C1,I1:I9)),) (array-entered)

=SMALL(MATCH("*"&C1&"*",I1:I9,0),1)

This works fine, however, when I try to use values of n>1 it returns a [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=NUM]#NUM [/URL] ! error. Why? I know the array consists of many variables, as if I delete datapoints in the array the return value changes. What am I doing wrong?

MATCH will return a scalar value N. SMALL(N,1) will be N, but SMALL(N,2) has no meaning and hence returns #NUM .

Try: =SMALL(IF(ISNUMBER(SEARCH(C1,I1:I9)),ROW(I1:I9)-ROW(I1)+1,""),1)

 

[DizzyDoodle]

Welcome to the Forum!



You're right. Match with wildcards won't work on strings >255 characters.

An alternative would be to use: =MATCH(TRUE,ISNUMBER(SEARCH(C1,I1:I9)),) (array-entered)



MATCH will return a scalar value N. SMALL(N,1) will be N, but SMALL(N,2) has no meaning and hence returns #NUM .

Try: =SMALL(IF(ISNUMBER(SEARCH(C1,I1:I9)),ROW(I1:I9)-ROW(I1)+1,""),1)

That's exactly what I was looking for, thanks ever so much Mr. Crump! I really owe you one. I had spent the whole day pulling my hair out over that one!