vlookup has all #na's even though there is a match

J

Janis

I'm in the current sheet, doing this formula on column AI a blank column. I
want to lookup values in column AH on the 98th percentile report column K
which is the 11th column on this other workbook shet?
=VLOOKUP(AH2,'[07-0714 Bay-Downstream Ports 98th Percentile.xls]Port
History'!$A:$K,11,FALSE)
Why doesn't it find any matches? I was extremely careful. I
even cleaned both columns in case there were any spaces. The cells have
groups of letters with a comma, ie. abcd, or abcd,efgh, . There are many
matches. It
should find matches on the other column with another set abcd, or abcd,efgh,

I can't figure out why I get all #na's. I can't even get a match but what
I really want to do is pull in the figures on column I if there is a match on
column K. How do I do that?
thanks,
 
J

John Bundy

Several possible issues, are you trying to look up a number stored as text
against a number stored as a number? Is column A on the lookup sheet the one
that contains the lokup items?(must be left most column)
For your second question make another column looking up the value you just
returned if not "" and return i
 
B

bj

there can also be non printing charaters which do not be removed with Trim()
and clean()
selet an apparent match (A20?)and
in an empty cell enter
='[07-0714 Bay-Downstream Ports 98th Percentile.xls]Port History'!$A20
see if it says what you want it to.
if it does
then
=AH2=='[07-0714 Bay-Downstream Ports 98th Percentile.xls]Port History'!$A20
from your response to the vlookupI assume it will say false (if it doesn't
reinstall excel)
then try
=len(AH2)
and
=len('[07-0714 Bay-Downstream Ports 98th Percentile.xls]Port History'!$A20)
they will probably be different but whether or not
try
=mid(AH2,1,1)=mid('[07-0714 Bay-Downstream Ports 98th Percentile.xls]Port
History'!$A20,1,1)
as long as the resullt is true change the 1,1 to 2,1 then 3,1 until the
result is false
once the result is false, you can use
=code(mid(AH2,1,1))
and
=code(mid('[07-0714 Bay-Downstream Ports 98th Percentile.xls]Port
History'!$A20,1,1))
to try to identify what the difference is.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top