index / match function

I’m trying to use this function:

=INDEX(‘WRP WK1’.$M$14:$M$312,MATCH(A16,‘WRP WK1’.$A$14:$A$314,1))

i know how it works, but I’m trying to MATCH names from one column to another, if the names are exactly the same it works.
But sometimes i have names that are formatted differently

example:

  1. Abbrederis, Jared

  2. Jared Abbrederis

how do I fix the MATCH part of the function to see different formats.

how do I have #1 find #2, and how do I have #2 find #1

clarification:

1.Abbrederis, Jared is A16 trying to find 2. Jared Abbrederis within $A$14:$A$314

how can this be done?

MATCH is for exact spelling in cells, but how do I lookup a name that is spelled correctly but in a different format.

Gets messy but you could check more than one possible match and use the match that was found. Following is only partial solution as example.

=INDEX($M$14:$M$312,IFNA(MATCH(E4,$A$14:$A$314,0),MATCH(SUBSTITUTE(MID(E4,FIND(",",E4,1) + 1,100)," ","")&" "&LEFT(E4,FIND(",",E4,1)-1),$A$14:$A$314,0)))

ty Mark,

E4 is supposed to be the Name Abbrederis, Jared . I replaced E4 with A16

=INDEX($M$14:$M$312,IFNA(MATCH(E4,$A$14:$A$314,0),MATCH(SUBSTITUTE(MID(E4,FIND(",",E4,1) + 1,100)," “,”")&" “&LEFT(E4,FIND(”,",E4,1)-1),$A$14:$A$314,0)))

returns a “0” value, not sure why, my indexing column is correct.


I tried another alternative (more time consuming) I was able to split the name “Text to Column” but not sure how to write to lookup two column matches