Excel 2007: Vlookup Problem
in [Functions]
|
From: ocean mist on 8 Jan 2010 16:16 I don't use Vlookup much and it's not working for me. I know it's user error so can you tell me what I did wrong? I have two worksheets in my file. In the first one, I have a list of addresses with a full unabbreviated US state name in column T, row 2. Column U is blank because I want to put the two-digit state code in there (starting with U2). In the second worksheet I have the full state name in Column A and the two-digit code for it in Column B (rows 1-50). Here's my formula starting in U2: =VLOOKUP(T2,StateCodes!$A$1:$B$50,2,FALSE) What is wrong?
From: Luke M on 8 Jan 2010 16:31 Your formula is correct in syntax and structure. Is it possible that there are extra spaces in the name that are causing errors? For example, if T2 contains "New York " this would cause the formula to fail (or unprintable characters, if info was copied from outside source like the internet). If so, you might try: =VLOOKUP(TRIM(CLEAN(T2)),StateCodes!$A$1:$B$50,2,FALSE) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "ocean mist" wrote: > I don't use Vlookup much and it's not working for me. I know it's user error > so can you tell me what I did wrong? > > I have two worksheets in my file. In the first one, I have a list of > addresses with a full unabbreviated US state name in column T, row 2. Column > U is blank because I want to put the two-digit state code in there (starting > with U2). > > In the second worksheet I have the full state name in Column A and the > two-digit code for it in Column B (rows 1-50). > > Here's my formula starting in U2: > > =VLOOKUP(T2,StateCodes!$A$1:$B$50,2,FALSE) > > What is wrong?
From: ocean mist on 8 Jan 2010 16:41 Drat! I tried your formula and it still doesn't work. New York is, of course, one of the states. Could it be because not all cells in column T have anything in them (yet)? "Luke M" wrote: > Your formula is correct in syntax and structure. > > Is it possible that there are extra spaces in the name that are causing > errors? For example, if T2 contains "New York " this would cause the formula > to fail (or unprintable characters, if info was copied from outside source > like the internet). If so, you might try: > > =VLOOKUP(TRIM(CLEAN(T2)),StateCodes!$A$1:$B$50,2,FALSE) > > -- > Best Regards, > > Luke M > *Remember to click "yes" if this post helped you!* > > > "ocean mist" wrote: > > > I don't use Vlookup much and it's not working for me. I know it's user error > > so can you tell me what I did wrong? > > > > I have two worksheets in my file. In the first one, I have a list of > > addresses with a full unabbreviated US state name in column T, row 2. Column > > U is blank because I want to put the two-digit state code in there (starting > > with U2). > > > > In the second worksheet I have the full state name in Column A and the > > two-digit code for it in Column B (rows 1-50). > > > > Here's my formula starting in U2: > > > > =VLOOKUP(T2,StateCodes!$A$1:$B$50,2,FALSE) > > > > What is wrong?
From: ocean mist on 8 Jan 2010 16:53 I found the problem. The look-up table was NOT clean. It had extra spaces after the state names, probably because it was copied from the Internet as you said. I took them out and it is working now. Many thanks for your help. "ocean mist" wrote: > Drat! I tried your formula and it still doesn't work. New York is, of course, > one of the states. Could it be because not all cells in column T have > anything in them (yet)? > > "Luke M" wrote: > > > Your formula is correct in syntax and structure. > > > > Is it possible that there are extra spaces in the name that are causing > > errors? For example, if T2 contains "New York " this would cause the formula > > to fail (or unprintable characters, if info was copied from outside source > > like the internet). If so, you might try: > > > > =VLOOKUP(TRIM(CLEAN(T2)),StateCodes!$A$1:$B$50,2,FALSE) > > > > -- > > Best Regards, > > > > Luke M > > *Remember to click "yes" if this post helped you!* > > > > > > "ocean mist" wrote: > > > > > I don't use Vlookup much and it's not working for me. I know it's user error > > > so can you tell me what I did wrong? > > > > > > I have two worksheets in my file. In the first one, I have a list of > > > addresses with a full unabbreviated US state name in column T, row 2. Column > > > U is blank because I want to put the two-digit state code in there (starting > > > with U2). > > > > > > In the second worksheet I have the full state name in Column A and the > > > two-digit code for it in Column B (rows 1-50). > > > > > > Here's my formula starting in U2: > > > > > > =VLOOKUP(T2,StateCodes!$A$1:$B$50,2,FALSE) > > > > > > What is wrong?
From: Gord Dibben on 8 Jan 2010 17:57
Sounds like should work as long as the full state names in T2:T51 match those in StateCodes A1:A50 Why do you say "it's not working"? Did you drag U2 down to U51? What results do you get? Is it possible you have extra space(s) in a state name in either sheet? Gord Dibben MS Excel MVP On Fri, 8 Jan 2010 13:16:01 -0800, ocean mist <oceanmist(a)discussions.microsoft.com> wrote: >I don't use Vlookup much and it's not working for me. I know it's user error >so can you tell me what I did wrong? > >I have two worksheets in my file. In the first one, I have a list of >addresses with a full unabbreviated US state name in column T, row 2. Column >U is blank because I want to put the two-digit state code in there (starting >with U2). > >In the second worksheet I have the full state name in Column A and the >two-digit code for it in Column B (rows 1-50). > >Here's my formula starting in U2: > >=VLOOKUP(T2,StateCodes!$A$1:$B$50,2,FALSE) > >What is wrong? |