Ask Your Question
0

index / match function [closed]

asked 2016-07-20 02:31:13 +0100

JG101 gravatar image

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

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by JG101
close date 2016-07-22 14:49:04.939314

2 Answers

Sort by » oldest newest most voted
0

answered 2016-07-20 05:22:47 +0100

JG101 gravatar image

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.

edit flag offensive delete link more
0

answered 2016-07-20 06:18:21 +0100

mark_t gravatar image

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)))
edit flag offensive delete link more

Comments

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

JG101 gravatar imageJG101 ( 2016-07-20 06:30:13 +0100 )edit

Question Tools

1 follower

Stats

Asked: 2016-07-20 02:31:13 +0100

Seen: 974 times

Last updated: Jul 20 '16