Results 1 to 7 of 7
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Vlookup problem pertaining to two sheets

    On sheet “Applicable Branch” Col H, I am trying to look up the branch code for the applicable stock number. The branch codes are on sheet "Stock Sheet" .

    Where "CB","AVI TRADERS","CN" appears on sheet "Stock Sheet", the formula must then look up the branch code on sheet "Branch Names not on Stock Sheet"

    The formula is not yielding the correct result for eg XXMRJB9CB should be returning CB2, XXMRJ1EG should be returning CN1 etc

    It would be appreciated if someone could kindly assist me
    Attached Files Attached Files
    Last edited by HowardC; 2016-07-26 at 00:09.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Chuck,

    Here's a formula that's a bit more readable (avoids the confusing, IMHO, SumProduct):
    =IF(bCheckBranch(VLOOKUP(B14,'Stock sheet'!$I$1:$T$43,12,FALSE)),VLOOKUP(B14,'Branch Names not on Stock Sheet'!$A$1:$B$8,2,FALSE),VLOOKUP(B14,'Stock sheet'!$I$1:$T$43,12,FALSE))

    You'll notice it uses a helper UDF bCheckBranch:
    Code:
    Option Explicit
    
    Function bCheckBranch(zBr As String) As Boolean
    
        bCheckBranch = IIf(InStr("CN*CB*AVI TRADERS", UCase(zBr)), -1, 0)
        
    End Function 'bCheckBranch
    Some observations:
    Try to use shorter sheet names when using code.
    Avoid spaces in sheet names, if readability is your goal use underlines vs spaces.
    Use Range names for your lookup (table) ranges makes code shorter and easier to adjust.

    I hope I understood what you were trying to accomplish.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    HowardC (2016-07-26)

  4. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi RG

    Thanks for your help and your comments, which I fully understand and appreciate

    Kindly amend your function to accept both upper and lower case


    Howard

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Howard,

    The function as it stands will accept either.

    You'll notice the UCase() function in there which takes the passed value and convert it to upper case for the purpose of the comparison.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #5
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    My Apologies. I had a type, so I thought that txt had to be in upper case

  7. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Howard

    aside from RG's method, you could use a 'helper column' to check the first lookup, then check the returned value against an entry in a named range.

    see attached file.

    I used a named range [block1] on a new sheet named [lookups], and a helper column [I].
    No coding required, and you can easily add more entries to the named range [block1] if required.

    zeddy
    Attached Files Attached Files

  8. The Following User Says Thank You to zeddy For This Useful Post:

    HowardC (2016-07-26)

  9. #7
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Thanks Zeddy

    This is also very useful


    Howard

Posting Permissions

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