Index match lookup formula

  • Thread starter Thread starter Todd Huttenstine
  • Start date Start date
T

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
 
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.
 
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?
 
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.
 
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)
 
Back
Top