VLOOKUP returning wrong cell?

[Dluxe]

Hi all... I'm sure tihs is a simple VLOOKUP problem, but I can't seem to
figure out the issue.

I have a lookup table of Nation abbreviations and the corresponding full
name:
IN India
IO British Indian Ocean Terr
IP Clipperton Island
IR Iran
IS Israel
IT Italy
IV Cote D'Ivoire
IZ Iraq

On a separate page, I have a listing of nations for people in a program (by
CODE). I want to use VLOOKUP to take the coded country and return the
nation name.

So,
A B C
IT [formula] Italy

I entered the following formula:
=VLOOKUP(A1,NationLookup,2)

The formula 'works', but not properly. Using the example above, it would
return ISRAEL instead of ITALY. It seems to be finding the right code, but
returning the value from one row up in the lookup table.

Anyone know how to fix this??

B

 

[Peo Sjoblom]

You need to use an exact match

=VLOOKUP(A1,NationLookup,2,FALSE)

or

=VLOOKUP(A1,NationLookup,2,0)

--
Regards,

Peo Sjoblom

(No private emails please)

 

[Ron Rosenfeld]

Hi all... I'm sure tihs is a simple VLOOKUP problem, but I can't seem to
figure out the issue.

I have a lookup table of Nation abbreviations and the corresponding full
name:
IN India
IO British Indian Ocean Terr
IP Clipperton Island
IR Iran
IS Israel
IT Italy
IV Cote D'Ivoire
IZ Iraq

On a separate page, I have a listing of nations for people in a program (by
CODE). I want to use VLOOKUP to take the coded country and return the
nation name.

So,
A B C
IT [formula] Italy

I entered the following formula:
=VLOOKUP(A1,NationLookup,2)

The formula 'works', but not properly. Using the example above, it would
return ISRAEL instead of ITALY. It seems to be finding the right code, but
returning the value from one row up in the lookup table.

Anyone know how to fix this??

B

Since your nation abbreviations are in alphabetical order, it is likely that
what you see in the abbreviation list is not what is really there.

For example, if you copied this table from an Internet (or html) page, it may
be that there is a trailing, non-printing space after each two-letter code.

This would give the behavior you describe and, if you tried to use VLOOKUP but
look for an exact match, would likely return #N/A.

Depending on exactly what is in the two-letter code cells, you could:

1. Clean up the code with a formula approach in a helper column; then
copy/paste special values back over the original column. Assume K1 has the
first two letter code, enter the formula and copy/drag down as needed.

=SUBSTITUTE(TRIM(K1),CHAR(160),"")

2. Clean up the code with a macro.

3. Modify the VLOOKUP formula to take into account the extra character(s).
For example:

=VLOOKUP(A1&char(160),NationLookup,2)


--ron

 

[Dluxe]

Peo,

Thanks so much for the response. When I include either the 0 or the FALSE,
I get back an #N/A error.

I've tried sorting the lists different ways to see if that makes it
better... But that doesn't seem to help...

Am I missing something?

Thank you again!!
B

You need to use an exact match

=VLOOKUP(A1,NationLookup,2,FALSE)

or

=VLOOKUP(A1,NationLookup,2,0)

--
Regards,

Peo Sjoblom

(No private emails please)

Hi all... I'm sure tihs is a simple VLOOKUP problem, but I can't seem to
figure out the issue.

I have a lookup table of Nation abbreviations and the corresponding full
name:
IN India
IO British Indian Ocean Terr
IP Clipperton Island
IR Iran
IS Israel
IT Italy
IV Cote D'Ivoire
IZ Iraq

On a separate page, I have a listing of nations for people in a program
(by CODE). I want to use VLOOKUP to take the coded country and return
the nation name.

So,
A B C
IT [formula] Italy

I entered the following formula:
=VLOOKUP(A1,NationLookup,2)

The formula 'works', but not properly. Using the example above, it would
return ISRAEL instead of ITALY. It seems to be finding the right code,
but returning the value from one row up in the lookup table.

Anyone know how to fix this??

B

 

[Dluxe]

Ron,

That was it EXACTLY... Thanks so much.

I used the TRIM and everything fell into place. I'll have to go back to the
source (a pull of a validation table in ORACLE) to figure out what was
causing the problem.

Thanks again!

B

Hi all... I'm sure tihs is a simple VLOOKUP problem, but I can't seem to
figure out the issue.

I have a lookup table of Nation abbreviations and the corresponding full
name:
IN India
IO British Indian Ocean Terr
IP Clipperton Island
IR Iran
IS Israel
IT Italy
IV Cote D'Ivoire
IZ Iraq

On a separate page, I have a listing of nations for people in a program
(by
CODE). I want to use VLOOKUP to take the coded country and return the
nation name.

So,
A B C
IT [formula] Italy

I entered the following formula:
=VLOOKUP(A1,NationLookup,2)

The formula 'works', but not properly. Using the example above, it would
return ISRAEL instead of ITALY. It seems to be finding the right code,
but
returning the value from one row up in the lookup table.

Anyone know how to fix this??

B

Since your nation abbreviations are in alphabetical order, it is likely
that
what you see in the abbreviation list is not what is really there.

For example, if you copied this table from an Internet (or html) page, it
may
be that there is a trailing, non-printing space after each two-letter
code.

This would give the behavior you describe and, if you tried to use VLOOKUP
but
look for an exact match, would likely return #N/A.

Depending on exactly what is in the two-letter code cells, you could:

1. Clean up the code with a formula approach in a helper column; then
copy/paste special values back over the original column. Assume K1 has
the
first two letter code, enter the formula and copy/drag down as needed.

=SUBSTITUTE(TRIM(K1),CHAR(160),"")

2. Clean up the code with a macro.

3. Modify the VLOOKUP formula to take into account the extra
character(s).
For example:

=VLOOKUP(A1&char(160),NationLookup,2)


--ron

 

[Ron Rosenfeld]

Ron,

That was it EXACTLY... Thanks so much.

I used the TRIM and everything fell into place. I'll have to go back to the
source (a pull of a validation table in ORACLE) to figure out what was
causing the problem.

Thanks again!

You are most welcome. I'm glad you've got things under control. Thank you for
the feedback.


--ron