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))

 

[Fluff]

Depends on what characters are there.
You can try

=substitute(A2,char(160),"")

 

[Toadstool]

Ah! I'm pretty sure Fluff has found it!
The most common issue when copy&pasting from a website is non-breaking spaces, Hex A0. You can search for those by using Ctrl-F and in the "Find what?" holding down the Alt key and tapping 0160 on your numeric keypad. You can then do a Replace All with nulls.

You could do a SEARCH for those characters by filling a column with =IFERROR(SEARCH(CHAR(160),D2),"") to get null or the character position if it exists.

 

[DavidGM]

This is really interesting and frustrating! Thanks guys! I've been using Excel for years now and never encountered hidden characters.

So, if I try Fluff's formula, it doesn't appear to rectify it, until I change it to substitute in a space (below) - as Toadstool's formula identified character position 34 which was a space, which the change in Fluff's formula then replaced.

Applying this across the two database results however, it only adds 3 more matches (including the erroneous B5 which I had highlighted). But I'm worried as to whether there's more ones that aren't matching that should be, as 3 across as 1000 sounds like few, considering how many were in the 20 I copied to here.

Is there any likelihood of there being different types of hidden characters? Or any suggestions as to any other things to do, or should I just assume there was only 3 incorrectly not matching until I discover otherwise? I tried the ctrl-f approach and it didn't appear to do anything.

=substitute(A2,char(160)," ")

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also do you have any strings that are longer than the ones you have posted?

 

[DavidGM]

Ah, apologies, I usually lurk so hadn't updated my details.

I believe my Excel version is 365 (64bit, version 2105), and Len suggests the longest string is 235 characters.

 

[Fluff]

Ok, try

=ISNUMBER(XMATCH(A2,C:C,0))

you might possibly get a few more that match.

 

[DavidGM]

Ah, got the same number of matches as with your previous formula, so hopefully it's okay then!

I guess I'll give it a go and come running back if I come across more errors with the matching!

 

[Fluff]

Ok, ? it works well.