Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup has all #na's even though there is a match
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, |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup has all #na's even though there is a match
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 -- -John Please rate when your question is answered to help us and others know what is helpful. "Janis" wrote: 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, |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup has all #na's even though there is a match
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. "Janis" wrote: 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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup #na's even though there are matches | Excel Discussion (Misc queries) | |||
Use Match or Vlookup? | Excel Discussion (Misc queries) | |||
Vlookup or Match | Excel Discussion (Misc queries) | |||
help vlookup,match | Excel Worksheet Functions | |||
Match or VLOOKUP | Excel Worksheet Functions |