Match/Countif/Lookup Not Identifying Matching Text

[DavidGM]

Hi,

I'm trying to do a systematic literature review, and reviewing article titles from several different databases. Therefore I'm trying to find a formula that identifies if there is a matching title, from a column of titles extracted from one database, to a column of titles extracted from another; to save me reviewing the same article multiple times.

I've tried a Countifs, Match, or VLOOKUP and it's not matching all titles, even when visually they are the same and I've removed all punctuation. Remove duplicates removes some, but not all. I can't see from any online searches where it's going wrong, hoping yourselves can help please!

I've posted an example below and highlighted a case that matches but I've not been able to match via a formula or through remove duplicates. Both columns are formatted as general.

Coping^J Suicide^J Adolescent.xlsx
	A	B	C
1	Database 1 Titles	Match?	Database 2 Titles
2	daily patterns in nonsuicidal self injury and coping among recently hospitalized youth at risk for suicide	TRUE	how to cope with perfectionism? perfectionism as a risk factor for suicidality and the role of cognitive coping in adolescents
3	mental health problems and needs among transitional age youth in indonesia	FALSE	daily patterns in nonsuicidal self injury and coping among recently hospitalized youth at risk for suicide
4	influence of coping strategies on the efficacy of yam youth aware of mental health a universal school based suicide preventive program	FALSE	cause of caregiver death and surviving caregiver coping style predict thwarted belongingness in bereaved youth
5	coping social support and suicide attempts among homeless adolescents	FALSE	coping with suicidal urges among youth seen in a psychiatric emergency department
6	the relationship between religious orientation coping strategies and suicidal behaviour	FALSE	how school counselors cope with student suicide a qualitative study
7	perceptions of lgbq+ youth and experts of suicide prevention video messages targeting lgbq+ youth qualitative study	FALSE	facteurs de protection reliés au risque suicidaire chez des adolescents comparaison de jeunes du milieu scolaire et de jeunes en centres jeunesse
8	it's not okay for you to call me that how sexual and gender minority youth cope with bullying victimization	FALSE	coping social support and suicide attempts among homeless adolescents
9	cause of caregiver death and surviving caregiver coping style predict thwarted belongingness in bereaved youth	TRUE	running to stand still trauma symptoms coping strategies and substance use behaviors in unaccompanied migrant youth
10	how do adolescents with adjustment disorder cope with stressful situations? relationship with suicidal risk	FALSE	an examination of help seeking attitudes and behaviors among asian american youth on multiple school campuses
11	a pilot evaluation of culture camps to increase alaska native youth wellness	FALSE	impulsivity kills some who did not plan to die by suicide evidence from chinese rural youths
12	is religiosity related to suicidal ideation among tunisian muslim youth after the january 14th revolution?	FALSE	in plain sight the public secrets of teenage cutting
13	why alternative teenagers self harm exploring the link between non suicidal self injury attempted suicide and adolescent identity	FALSE	estilos de afrontamiento como predicadores del riesgo suicida en estudiantes adolescentes
14	coping with suicidal urges among youth seen in a psychiatric emergency department	TRUE	teenagers' attitudes about coping strategies and help seeking behavior for suicidality
15	african american adolescent suicidal ideation and behavior the role of racism and prevention	FALSE	strategies employed by sexual minority adolescents to cope with minority stress
16	teenagers' attitudes about coping strategies and help seeking behavior for suicidality	TRUE	childhood sexual abuse and current suicidal ideation among adolescents problem focused and emotion focused coping skills
17	risk and protective factors for suicide among guyanese youth youth and stakeholder perspectives	FALSE	dysregulation catastrophic reactions and the anxiety disorders
18	cohort profile the youth and mental health study yamhs a longitudinal study of the period from adolescence to adulthood	FALSE	bullying victimization bullying witnessing bullying perpetration and suicide risk among adolescents a serial mediation analysis
19	exploring presentation differences in multi cultural youth seeking assistance for mental health problems	FALSE	non suicidal self injury among male adjudicated adolescents psychosocial concerns coping responses diagnoses and functions
20	how many times did i not want to live a life because of him the complex connections between child sexual abuse disclosure and self injurious thoughts and behaviors	FALSE	associations between depression anxiety stress hopelessness subjective well being coping styles and suicide in chinese university students
Sheet7

Cell Formulas
Range	Formula
B2:B20	B2	=ISNUMBER(MATCH(A2,C:C,0))

 

[Radoslaw Poprawski]

i dont get it.
in B you actually found everything that matches.
by the way how this formula is created every "TRUE" tells you that this corresponding row from A1 to A20 exists in column C.
Every FALSE means its not in column C at all.


Maybe that's what ur looking for?

Book3
	A	B	C	D
1	Database 1 Titles	Match?		Database 2 Titles
2	daily patterns in nonsuicidal self injury and coping among recently hospitalized youth at risk for suicide	TRUE	Article does not exists in column A	how to cope with perfectionism? perfectionism as a risk factor for suicidality and the role of cognitive coping in adolescents
3	mental health problems and needs among transitional age youth in indonesia	FALSE	Title from Cell: D3 Does exists in: A2	daily patterns in nonsuicidal self injury and coping among recently hospitalized youth at risk for suicide
4	influence of coping strategies on the efficacy of yam youth aware of mental health a universal school based suicide preventive program	FALSE	Title from Cell: D4 Does exists in: A9	cause of caregiver death and surviving caregiver coping style predict thwarted belongingness in bereaved youth
5	coping social support and suicide attempts among homeless adolescents	TRUE	Title from Cell: D5 Does exists in: A14	coping with suicidal urges among youth seen in a psychiatric emergency department
6	the relationship between religious orientation coping strategies and suicidal behaviour	FALSE	Article does not exists in column A	how school counselors cope with student suicide a qualitative study
7	perceptions of lgbq+ youth and experts of suicide prevention video messages targeting lgbq+ youth qualitative study	FALSE	Article does not exists in column A	facteurs de protection reliés au risque suicidaire chez des adolescents comparaison de jeunes du milieu scolaire et de jeunes en centres jeunesse
8	it's not okay for you to call me that how sexual and gender minority youth cope with bullying victimization	FALSE	Title from Cell: D8 Does exists in: A5	coping social support and suicide attempts among homeless adolescents
9	cause of caregiver death and surviving caregiver coping style predict thwarted belongingness in bereaved youth	TRUE	Article does not exists in column A	running to stand still trauma symptoms coping strategies and substance use behaviors in unaccompanied migrant youth
10	how do adolescents with adjustment disorder cope with stressful situations? relationship with suicidal risk	FALSE	Article does not exists in column A	an examination of help seeking attitudes and behaviors among asian american youth on multiple school campuses
11	a pilot evaluation of culture camps to increase alaska native youth wellness	FALSE	Article does not exists in column A	impulsivity kills some who did not plan to die by suicide evidence from chinese rural youths
12	is religiosity related to suicidal ideation among tunisian muslim youth after the january 14th revolution?	FALSE	Article does not exists in column A	in plain sight the public secrets of teenage cutting
13	why alternative teenagers self harm exploring the link between non suicidal self injury attempted suicide and adolescent identity	FALSE	Article does not exists in column A	estilos de afrontamiento como predicadores del riesgo suicida en estudiantes adolescentes
14	coping with suicidal urges among youth seen in a psychiatric emergency department	TRUE	Title from Cell: D14 Does exists in: A16	teenagers' attitudes about coping strategies and help seeking behavior for suicidality
15	african american adolescent suicidal ideation and behavior the role of racism and prevention	FALSE	Article does not exists in column A	strategies employed by sexual minority adolescents to cope with minority stress
16	teenagers' attitudes about coping strategies and help seeking behavior for suicidality	TRUE	Article does not exists in column A	childhood sexual abuse and current suicidal ideation among adolescents problem focused and emotion focused coping skills
17	risk and protective factors for suicide among guyanese youth youth and stakeholder perspectives	FALSE	Article does not exists in column A	dysregulation catastrophic reactions and the anxiety disorders
18	cohort profile the youth and mental health study yamhs a longitudinal study of the period from adolescence to adulthood	FALSE	Article does not exists in column A	bullying victimization bullying witnessing bullying perpetration and suicide risk among adolescents a serial mediation analysis
19	exploring presentation differences in multi cultural youth seeking assistance for mental health problems	FALSE	Article does not exists in column A	non suicidal self injury among male adjudicated adolescents psychosocial concerns coping responses diagnoses and functions
20	how many times did i not want to live a life because of him the complex connections between child sexual abuse disclosure and self injurious thoughts and behaviors	FALSE	Article does not exists in column A	associations between depression anxiety stress hopelessness subjective well being coping styles and suicide in chinese university students
Sheet1

Cell Formulas
Range	Formula
B2:B20	B2	=ISNUMBER(MATCH(A2,D:D,0))
C2	C2	=IFERROR("Title from Cell: "&ADDRESS(ROW(D2),COLUMN(D2),4,1)&"Does exists in: "&MATCH(D2,A:A,0),"Article does not exists in column A")
C3:C20	C3	=IFERROR("Title from Cell: "&ADDRESS(ROW(D3),COLUMN(D3),4,1)&" Does exists in: "&ADDRESS(MATCH(D3,A:A,0),1,4,1),"Article does not exists in column A")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A20	Expression	="ISNUMBER(MATCH($A2;$C:$C;0))"	text	NO

 

[Toadstool]

Hi DavidGM,

As Radoslaw pointed out there is a match for both highlit cells. Would this approach be easier?

DavidGM.xlsx
	A	B	C	D
1	Match DB1 to DB 2	Database 1 Titles	Match DB2 to DB 1	Database 2 Titles
2	3	daily patterns in nonsuicidal self injury and coping among recently hospitalized youth at risk for suicide	No match->	how to cope with perfectionism? perfectionism as a risk factor for suicidality and the role of cognitive coping in adolescents
3	No match->	mental health problems and needs among transitional age youth in indonesia	2	daily patterns in nonsuicidal self injury and coping among recently hospitalized youth at risk for suicide
4	No match->	influence of coping strategies on the efficacy of yam youth aware of mental health a universal school based suicide preventive program	9	cause of caregiver death and surviving caregiver coping style predict thwarted belongingness in bereaved youth
5	8	coping social support and suicide attempts among homeless adolescents	14	coping with suicidal urges among youth seen in a psychiatric emergency department
6	No match->	the relationship between religious orientation coping strategies and suicidal behaviour	No match->	how school counselors cope with student suicide a qualitative study
7	No match->	perceptions of lgbq+ youth and experts of suicide prevention video messages targeting lgbq+ youth qualitative study	No match->	facteurs de protection reliés au risque suicidaire chez des adolescents comparaison de jeunes du milieu scolaire et de jeunes en centres jeunesse
8	No match->	it's not okay for you to call me that how sexual and gender minority youth cope with bullying victimization	5	coping social support and suicide attempts among homeless adolescents
9	4	cause of caregiver death and surviving caregiver coping style predict thwarted belongingness in bereaved youth	No match->	running to stand still trauma symptoms coping strategies and substance use behaviors in unaccompanied migrant youth
10	No match->	how do adolescents with adjustment disorder cope with stressful situations? relationship with suicidal risk	No match->	an examination of help seeking attitudes and behaviors among asian american youth on multiple school campuses
11	No match->	a pilot evaluation of culture camps to increase alaska native youth wellness	No match->	impulsivity kills some who did not plan to die by suicide evidence from chinese rural youths
12	No match->	is religiosity related to suicidal ideation among tunisian muslim youth after the january 14th revolution?	No match->	in plain sight the public secrets of teenage cutting
13	No match->	why alternative teenagers self harm exploring the link between non suicidal self injury attempted suicide and adolescent identity	No match->	estilos de afrontamiento como predicadores del riesgo suicida en estudiantes adolescentes
14	5	coping with suicidal urges among youth seen in a psychiatric emergency department	16	teenagers' attitudes about coping strategies and help seeking behavior for suicidality
15	No match->	african american adolescent suicidal ideation and behavior the role of racism and prevention	No match->	strategies employed by sexual minority adolescents to cope with minority stress
16	14	teenagers' attitudes about coping strategies and help seeking behavior for suicidality	No match->	childhood sexual abuse and current suicidal ideation among adolescents problem focused and emotion focused coping skills
17	No match->	risk and protective factors for suicide among guyanese youth youth and stakeholder perspectives	No match->	dysregulation catastrophic reactions and the anxiety disorders
18	No match->	cohort profile the youth and mental health study yamhs a longitudinal study of the period from adolescence to adulthood	No match->	bullying victimization bullying witnessing bullying perpetration and suicide risk among adolescents a serial mediation analysis
19	No match->	exploring presentation differences in multi cultural youth seeking assistance for mental health problems	No match->	non suicidal self injury among male adjudicated adolescents psychosocial concerns coping responses diagnoses and functions
20	No match->	how many times did i not want to live a life because of him the complex connections between child sexual abuse disclosure and self injurious thoughts and behaviors	No match->	associations between depression anxiety stress hopelessness subjective well being coping styles and suicide in chinese university students
Sheet1

Cell Formulas
Range	Formula
A2:A20	A2	=IF(ISNA(MATCH(B2,D:D,0)),"No match->",MATCH(B2,D:D,0))
C2:C20	C2	=IF(ISNA(MATCH(D2,B:B,0)),"No match->",MATCH(D2,B:B,0))

 

[DavidGM]

Hi,

Thanks so much for the help from both of you.

I'm confused regarding the difference between your match formulas and my match formula, as the core of the formula is the same between them. Yet, my match formula isn't matching the highlighted article, and nor can I match the highlighted article when I copy your formula into a spreadsheet.

I'm not sure why my B5 is not matching in a spreadsheet or on MrExcel, yet Radalow's and Toadstool's is matching on MrExcel, but not matching when I apply it to a spreadsheet?

Curious as to whether my B5 formula is also not matching as yourselves see it, and whether there's something with my Excel set up...?

 

[Radoslaw Poprawski]

i dont know either?
Reset ur excel and start fresh?
Reset ur PC?
Try cleaning out temp data?

 

[DavidGM]

Interesting that it does sound like it's an anomaly, rather than anything formula based.

I've tried it within 2 different computers, turned off and on and in different spreadsheets, and it's not resolved it. I'll look up how to clear temp data.

 

[Fluff]

If you copy the mini sheet from either post#2 or post#3 using the copy icon & than paste it into a blank sheet, do the formulae work?

 

[DavidGM]

Great idea, just tried that. So if I copy the mini sheet from post #2 or #3 into a blank sheet the formulae work... Until I copy and paste in the full list of articles (around 1000 rows) into it. Regardless of whether it's added to a blank workbook or existing workbook. This also doesn't produce a match even when only one list of articles from a database is copied across to the formulae from the mini sheet: the same article turned from a match to not a match as I did it.

I've saved part of a screenshot below just to prove I'm not hallucinating. As you can see, A5's formula is unchanged but not producing a match of B5 to C8. It's the same for Radoslaw's formula.

 

[Fluff]

In that case I suspect that you have got some hidden characters in the data that get stripped out when you post it as a minisheet.
Is the data copied form a web site?

 

[DavidGM]

The data is from a website, but exported as an XML (that the website sends). I then applied a formula from Exceljet (below) to remove any formatting (although it wasn't matching appropriately before this formula was applied). It also doesn't match if I paste it into a notebook or anything and then copy and paste it back into Excel.

But, If I manually type out "coping social support and suicide attempts among homeless adolescents" in B5, it then matches with D8, even if I don't manually type D8.

Is there any way around the hidden characters or this issue that you are aware of? As manually re-typing or searching any duplicated titles will be laborious across 2000 further rows

=LOWER(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(K2,"("," "),")"," "),"-"," "),":"," "),";"," "),"!"," "),","," "),"."," ")))