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.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

RasGhul

Well-known Member
Joined
Jul 15, 2016
Messages
797
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,743
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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,826
Messages
5,855,873
Members
431,771
Latest member
CoryMelth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top