+ Reply to Thread
Results 1 to 14 of 14

VLOOKUP prob with Multiple Wks

  1. #1
    Forum Contributor
    Join Date
    02-14-2004
    Posts
    131

    VLOOKUP prob with Multiple Wks

    I have a spreadsheet with 5 worksheets. I want VLookup to read the City and State in the two columns next to the County Column in the custmast worksheet. Compare the city and state to the worksheets AL, FL and GA worksheets and return the correct County and Territory Code found in the Territory worksheet in the County Column and Territory Column in custmast worksheet. I've attached a file for better understanding.
    Attached Files Attached Files

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    Microsoft 365
    Posts
    14,701
    You have a couple of problems with this I think. AL worksheet appears to have Cities in column B and counties in column A but the other two seem to be the other way round (although headers are the same).

    You need these to be consistent and for VLOOKUP to work you need cities in one column (e.g. A) and counties in a column to the right (e.g. B).

    You also appear to have a space in the AL worksheet name, i.e. AL[space], this complicates things further.....

    sort out these problems and you should be able to use a formula like this in custmast K2 copied down the column

    =VLOOKUP(E2,INDIRECT(F2&"!A2:B1000"),2,0)

    As it stands you need to use this

    =INDEX(INDIRECT("'"&F2&" '!a2:a1000"),MATCH(E2,INDIRECT("'"&F2&" '!b2:b1000"),0))

    ....and it still won't work on all entries

  3. #3
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by nander
    I have a spreadsheet with 5 worksheets. I want VLookup to read the City and State in the two columns next to the County Column in the custmast worksheet. Compare the city and state to the worksheets AL, FL and GA worksheets and return the correct County and Territory Code found in the Territory worksheet in the County Column and Territory Column in custmast worksheet. I've attached a file for better understanding.
    Hi,

    try

    =IF(ISERROR(VLOOKUP(E2,INDIRECT(F2&"!A:C"),2,FALSE)),"not found",VLOOKUP(E2,INDIRECT(F2&"!A:C"),2,FALSE))

    It is recommended that you do not use trailing spaces in your Sheetnames, and (recommended but not necessary) that the three lookup tables be kept in the same format.

    The FL table seems to be under some influence of Filter, Data, ShowAll is required to see all entries.

    hth
    ---
    added hi daddylonglegs.

    forgot to mention, the book has a number of invalid Named Ranges, these should probably be removed.

    (looks a little better than my first mis-read attempt at this ?)
    ---
    Last edited by Bryan Hessey; 03-12-2007 at 08:20 PM.
    Si fractum non sit, noli id reficere.

  4. #4
    Forum Contributor
    Join Date
    02-14-2004
    Posts
    131

    Update

    Guys thanks for the help. I've corrected the Column A & B mix-up. Removed the trailing space in AL{space} and applied the VLOOKUP formula. However it only works on the AL worksheet. The custmast contains FL and GA counties too. and the Territory column in custmast needs to have a corresponding territory code from the territory worksheet too. Thanks again for your help. I've included the updated file.
    Last edited by nander; 03-12-2007 at 09:12 PM.

  5. #5
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by nander
    Guys thanks for the help. I've corrected the Column A & B mix-up. Removed the trailing space in AL{space} and applied the VLOOKUP formula. However it only works on the AL worksheet. The custmast contains FL and GA counties too. and the Territory column in custmast needs to have a corresponding territory code from the territory worksheet too. Thanks again for your help. I've included the updated file.
    Hi,

    it works from all three sheets on my copy, which formula did you use?

    The Territory is a straight VLookup, same format as the standard vlookup.

    No file was found attached.

    ---

  6. #6
    Forum Contributor
    Join Date
    02-14-2004
    Posts
    131
    Okay, sorry about the missing file. I was to big to attach. I tried the formula again and it works for all AL, FL and GA. Thank you very much. I realize the Territory code is straightforward but would you be so kind as to provide the formula? Like I said I'm not that familiar with the use of vlook and struggle understand the basics. Thanks

  7. #7
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by nander
    Okay, sorry about the missing file. I was to big to attach. I tried the formula again and it works for all AL, FL and GA. Thank you very much. I realize the Territory code is straightforward but would you be so kind as to provide the formula? Like I said I'm not that familiar with the use of vlook and struggle understand the basics. Thanks
    Hi,

    good to see one bit worked.

    The territory, the formula is easy but I have no concept of your 'Territory', you could try

    =VLookup(K2,Territory!A:C,3,False)


    hth
    ---
    Last edited by Bryan Hessey; 03-12-2007 at 11:14 PM.

  8. #8
    Forum Contributor
    Join Date
    02-14-2004
    Posts
    131

    VLOOKUP help

    Brian thanks for your help thus far. I do have a bit of a snag on the last part with the Territory. The main problem is that the territory function you gave me only works on certain county/state/territory code combinations other wise I get a #N/A result. Also my file is too large to post. Could I send it to you for your review? Thanks.

  9. #9
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by nander
    Bryan thanks for your help thus far. I do have a bit of a snag on the last part with the Territory. The main problem is that the territory function you gave me only works on certain county/state/territory code combinations other wise I get a #N/A result. Also my file is too large to post. Could I send it to you for your review? Thanks.
    Hi,

    for the territory, in L2 of Custmast put

    =VLOOKUP(E2,IF(territory!B$1:B$50=F2,territory!A$1:C$50),3,FALSE)

    CSE (CTRL/Shift/Enter) to lookup on two values.

    amend the $1:~$50 to suit your range)
    hth
    ---

  10. #10
    Forum Contributor
    Join Date
    02-14-2004
    Posts
    131

    Tried your suggestion for Territory

    Still have a problem with that last row. Would you take a look? Thanks. I attached the file with the changes you suggested in custmast.
    Attached Files Attached Files

  11. #11
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by nander
    Still have a problem with that last row. Would you take a look? Thanks. I attached the file with the changes you suggested in custmast.
    no, I suggested that you use CSE to enter the formula, which you need to do.

    I also stated earlier that I have no idea on your territories nor what you are doing.

    Are you looking up a City in the Territories list? and if so are BULLOCK, COFFEE, CONECUH, COVINGTON, CRENSHAW & DALE cities in AL ?

    or are you supposed to be looking up something like HENRY, COVINGTON, HOUSTON, LEE & PIKE from the K column?

    Perhaps the correct formula will prove more successful, ie, =VLOOKUP(K2
    rather than E2.

    hth
    ---

  12. #12
    Forum Contributor
    Join Date
    02-14-2004
    Posts
    131
    Bryan, I'm not familiar with using CSE to enter a function. I'm assuming I press these keys and then key the formula into the cell. I've just been cutting and pasting the formula from this forum into the cell. The Territory worksheet contains a in the Territory that I want in the custmast worksheet column L. What I'm wanting VLOOKUP to do is read the County and State in custmast and then look for the matching County State match in the Territory worksheet and then write the corresponding Territory Code in column L. I'll try the process again. Thanks

  13. #13
    Forum Contributor
    Join Date
    02-14-2004
    Posts
    131
    Just tried the last suggestion of using K2 and the Territory Codes from the Territory worksheet appeared in column L in custmast. Great, now the only thing that I get are a few #N/A and that is because I don't have a corresponding county state in the Territory worksheet. I have a catch-all in the Territory called All Territories with a code of 99. What would I change the formula to to get this 99 when the formula returns a value of #N/A. THANKS

  14. #14
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by nander
    Just tried the last suggestion of using K2 and the Territory Codes from the Territory worksheet appeared in column L in custmast. Great, now the only thing that I get are a few #N/A and that is because I don't have a corresponding county state in the Territory worksheet. I have a catch-all in the Territory called All Territories with a code of 99. What would I change the formula to to get this 99 when the formula returns a value of #N/A. THANKS
    that is done via the 'IsError' option.

    You have a lookup. See post #3 where a 'IsError' was used, and format your new VLookup the same way, ie, =If(IsError( yourLookup ), 99 , yourLookup )

    Let me know how you go.
    ---

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1