I'm using a Vlookup for states that correspond to addresses.. well if possible I would like this run off of the state that they input, but the only problem is that they input the state as in address, state, zipcode into another cell...is their anyway that I can link these two together....i'm guessing something like ..an if"state" = state in the validation, that it would work...maybe i'm talking out my but
=Vlookup(TRIM(MID(A1,FIND(",",A1)+1,FIND(",",MID(A1,FIND(",",A1)+1,99))-1)),Sheet2!A1:B999,1,False)Originally Posted by rjahr01
should get you the state if it exists, and if the data entered has 2 commas etc
assuming that yout data is in column A, and your States are listed on Sheet2 etc . . .
If you want to trap any errors, then
=IF(ISERROR(VLOOKUP(TRIM(MID(A1,FIND(",",A1)+1,FIND(",",MID(A1,FIND(",",A1)+1,99))-1)),Sheet2!A1:B999,1,FALSE)),"error",VLOOKUP(TRIM(MID(A1,FIND(",",A1)+1,FIND(",",MID(A1,FIND(",",A1)+1,99))-1)),Sheet2!A1:B999,1,FALSE))
hth
---
Unless you meant that the State was in a separate column to the Address and zipcode?
You can split the 3 items into separate columns by Data, 'Text to Columns' if required, and then VLookup just the State as
=Vlookup(state-column, table:range, 1, False)
---
Last edited by Bryan Hessey; 11-07-2006 at 11:59 PM.
Si fractum non sit, noli id reficere.
I'm getting a value sign..
i'm having a bit of a problem...first off the address line will only have one comma i.e. Louisville, KY 40218 so i'm assuming I would have to remove one of those find statements in your formula....also i'm still getting an error message. Essentially i have all of my states in A52-A104 and the addresses in the corresponding cell to the right..cell A13 is the reference cell with the city, state zip code
This is my formula right now
=VLOOKUP(TRIM(MID(A13,FIND(",",A13)+1,FIND(",",MID(A13,FIND(",",A13)+1,99))-1)),A52:C104,2,FALSE)
- well . . not really, the format stated was "input the state as in address, state, zipcode" - however I can adjust that.try...also i'm still getting an error message. Essentially i have all of my states in A52-A104 and the addresses in the corresponding cell to the right..cell A13 is the reference cell with the city, state zip code
This is my formula right now
=VLOOKUP(TRIM(MID(A13,FIND(",",A13)+1,FIND(",",MID(A13,FIND(",",A13)+1,99))-1)),A52:C104,2,FALSE)
=VLOOKUP(TRIM(MID(A13,FIND(",",A13)+1,FIND(" ",TRIM(MID(A13,FIND(",",A13)+1,99)))-0)),A$52:C$104,2,FALSE)
Hopefully your states are all single words.
--
Last edited by Bryan Hessey; 11-09-2006 at 12:09 AM.
Revised post************
all of the states are single words, it didn't work until i changed it to a "true" statement and then the Kentucky popped up, but I still need to get it to relate to the corresponding address for the cell to the right of each state. Thanks so much for the help...it really is appreciated
Last edited by rjahr01; 11-09-2006 at 12:16 AM.
Ok I got it
=VLOOKUP(A51,A52:C104,2,FALSE)
=VLOOKUP(TRIM(MID(A13,FIND(",",A13)+1,FIND(" ",TRIM(MID(A13,FIND(",",A13)+1,99)))-0)),A52:C104,1,TRUE)
So the first one went into the cell box for where the address should go, the second one is the routing cell to get the state seperated and put down...its all hidden..thanks so much
True is incorrect for use here, (ie do NOT use =True)
did my
=VLOOKUP(TRIM(MID(A13,FIND(",",A13)+1,FIND(" ",TRIM(MID(A13,FIND(",",A13)+1,99)))-0)),A$52:C$104,2,FALSE)
not work?
the red =1 will return column A, =2 column B, =3 column C
as per the attached.
Last edited by Bryan Hessey; 11-09-2006 at 02:03 AM.
But see when I use yours I get the word State w/ the "FALSE" and I want to get the name of the city it will correspond to...so in your zip file if you change your statement to true you receive the correct state for that lookup, and then from there i corresponded that to another v statement to pull up the address for that exact state. I dunno
It does not need change, the zip file works, what else were you expecting it to do ?
to get the name of the city it will correspond to... << where are the 'city' ?
can you post two rows of data and two rows 52 - 54 ?
--
Last edited by Bryan Hessey; 11-09-2006 at 08:46 AM.
oh ok..your right my bad the false statement works now...for some reason i must have had something wrong before where the false was not working and the true gave me an address...
last question, I just ran into this....if people don't use the right spelling i.e. they dont' abrievate like their supposed to...so instead of KY they spell Kentucky... I figured to do this I would simply double the list up to include abbrievations I.e. AL, Alaska,AR,Arkansas and just do double the address's and increase the list of the range...
oh quick last problem i forgot to add...oh and thanks i'm learning so much....
addresses are normally setup like
Mr. Name
Apt1
123 Street
Lexington, KY 40218
or
Mr. Name
123 Street
Lexington, Ky 40218
i.e..sometimes it is in line a4 and sometimes in line a3....is their anyway to get the list to read from both cells as long as it contains a state name
Where do you want the state (valid or error noted) to appear?
Is there a header row?
Does the first name start at Row 2?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks