Index match lookup formula
 

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

Index match lookup formula
 

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.

--
Regards,
Tom Ogilvy




"Todd Huttenstine" wrote in message
...
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

Index match lookup formula
 

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?

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

--
Regards,
Tom Ogilvy




"Todd Huttenstine"
wrote in message
...
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


.

Index match lookup formula
 

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.

--
Regards,
Tom Ogilvy

"Todd Huttenstine" wrote in message
...
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?

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

--
Regards,
Tom Ogilvy




"Todd Huttenstine"
wrote in message
...
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


.

Index match lookup formula
 

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)

--
Regards
Frank Kabel
Frankfurt, Germany


Todd Huttenstine wrote:
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?

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

--
Regards,
Tom Ogilvy




"Todd Huttenstine" wrote in
message ...
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


.