CODEWITHSUNDEEP
excelindexingmatch
Nikana
Nikana

Reputation: 9

Excel Find the Closest Text Value

I have data collected from an website form, where parents are required to type in the school their child attends. Unfortunately there are many different text combinations for the same school, and I need to find the closest school name as possible.

Consider the following example:

          A                         B
     Parents Typing        Official Names of School
     ----------------------------------------------
1    Erin Woods School        Erin Woods School
2    Erin Woods Elementary        St. Catherine Elementary School
3    Erin Woods        Monsignor A. J. Hetherington Elementary School
4    Erinwoods elementary        Catherine Nichols Gunn School
5    Erin Woods        Huntington Hills School
6    ERINWOODS SCHOOL        Mayland Heights School
7    ERINWOORDS SCHOOL        Mount View School
8    erinwoods school        North Haven School
9    Erin wood school         Queen Elizabeth School
10    Erinwoods Elementary        Ecole de la Rose Sauvage

I have 10 different ways of parents identifying "Erin Woods School". My attempts as using an 'index'&'match' combination has limited success, and can often pick up other school names (as illustrated in column 'B').

Perhaps there's an answer here, but I am unable to get it to work. Excel Find the largest partial value in an indexed list

Any help would be greatly appreciated, as I have over 20 schools to comb through, and re-typing the parents entries would be exhausting.

Upvotes: 0

Views: 352

Answers (1)

pnuts
pnuts

Reputation: 59495

For your sample data the following should work:

=IF(SEARCH("woo",A2),$B$2)

copied down to suit.

Maybe you should provide parents with your (numbered) list of schools and ask them to enter a number instead.

Upvotes: 1

Related Questions