Problem with Match function...

DizzyDoodle

New Member
Joined
Mar 24, 2019
Messages
2
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)

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 =
-------------------------------------------------------------------------------------------------------
Coal Advisory Group#X00807#Reinhold Elsen#http://ec.europa.eu/transparency/re...ail.groupDetail&groupID=807#Member#B#Industry
-------------------------------------------------------------------------------------------------------
<strike></strike>

<colgroup><col></colgroup><tbody>
</tbody>
<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:
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>

<colgroup><col></colgroup><tbody>
</tbody>
<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.
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

RasGhul

Well-known Member
Joined
Jul 15, 2016
Messages
609
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

Well-known Member
Joined
Sep 18, 2013
Messages
3,801
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 24, 2019
Messages
2
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,108,985
Messages
5,526,063
Members
409,685
Latest member
Davetom

This Week's Hot Topics

Top