Index match lookup formula

[Todd Huttenstine]

Below is a formula that indexes column F:F. It looks in
Column N:N(Column N:N contains names" for the lookup value
and matches it with a value in column G:G and returns the
value in Column F:F.
=INDEX(F:F,MATCH(N:N,G:G,0))

If it finds a match it obviously returns the value but now
heres the problem... Now not all names in column N:N
match exactly with the name in Column G:G and I do not see
where it would be possible or practical to change the
names to match since they are all coming out of a
database. However, I know there is a way to return the
nearest/closest match. For example lets say the value in
column N is "Todd Huttenstine" and the value in column G
is "Tod Huttensti". Eventhough this is not a perfect
match, it is very close and I would want the formula to
know this.

How do I do this?


Thank you
Todd Huttenstine

 

[Tom Ogilvy]

change the third argument in match to a 1 rather than a 0. This assumes
your data is sorted ascending.

See help for details.

There is not fuzzy search capability built in.

 

[Todd Huttenstine]

Thanx I did that but it returns the value 1 cell above the
actual value it needs to pull back. Why is it doing this?

 

[Tom Ogilvy]

The help spells the behavior of match using other than 0 as the third
argument.

Since you have your data readily available, you should be able to decipher
the explanation in terms of the data you are applying it against.

 

[Frank Kabel]

Hi Todd
either you have messed the ranges a little bit (post your exact
formula) or the value is considered as match. Please post both values
(the one returned and your expected value)