Results 1 to 12 of 12
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,544
    Thanks
    145
    Thanked 7 Times in 7 Posts

    Vlookup not giving correct result

    I have text items in Column A


    I need to look up the department in Col I for each text item in Col A, based on the table in H1:I2.The items in I2 contains part of the text that is in col A


    I need a Vlookup or another formula to extract the dept based on the text in Col A


    Your assistance in this regard is most appreciated
    Attached Files Attached Files

  2. #2
    Silver Lounger kweaver's Avatar
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,653
    Thanks
    53
    Thanked 83 Times in 77 Posts
    Your post said that the text in column I is partially in column A...I don't see how any of the text in I matches anything in A.

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,544
    Thanks
    145
    Thanked 7 Times in 7 Posts
    Hi Kweaver

    Sorry for being unclear. What I am trying to do is lookup a value in say A2 and0 return the applicable for eg Dept1

    The first part of the look up table only contains for eg Internal but A2 contains "sales Sales Internal Shirts". The formula must be able to look up the item Internal from the text within A2 for eg and return Dept1

  4. #4
    Silver Lounger kweaver's Avatar
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,653
    Thanks
    53
    Thanked 83 Times in 77 Posts
    I used the following formula in B2 and filled down.

    Code:
    =INDEX($I$1:$I$4,SUMPRODUCT(ISNUMBER(SEARCH($H$2:$H$4&" ",A2&" "))*ROW($2:$4)),1)
    Change the references to I4 and H4 and $4 to another number should you have more items and more departments.

    My formula will give #VALUE! if no item is found. So you might want to incorporate an IFERROR instruction such as:

    Code:
    =IFERROR(INDEX($I$1:$I$4,SUMPRODUCT(ISNUMBER(SEARCH($H$2:$H$4&" ",A2&" "))*ROW($2:$4)),1),"")
    Last edited by kweaver; 2018-06-13 at 13:36.

  5. The Following User Says Thank You to kweaver For This Useful Post:

    Maudibe (2018-06-13)

  6. #5
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,544
    Thanks
    145
    Thanked 7 Times in 7 Posts
    Thanks for the help, Kweaver. Your formula works perfectly


    Please explain the section after index .....
    Code:
     SUMPRODUCT(ISNUMBER(SEARCH($H$2:$H$3&" ",A2&" "))*ROW($2:$3)),1),"")

  7. #6
    Silver Lounger kweaver's Avatar
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,653
    Thanks
    53
    Thanked 83 Times in 77 Posts
    I put a space after the item text in column H and after the Detailed Item text in column A.
    I did that because I wanted to be sure of an exact match of the item. Without a space, something like "internals" (with the "s") or "Interjection" would be found.
    The space after the text in column A allows for the Item you're looking for to be at the end of the text string in column A (such as the case for row 3: Sale MKT1 Int).

    If you select the SEARCH section (including the parens) and touch F9, you'll see what the result of the search yields. When there's a match, there will be a position value of the match (which you don't really care about). However, when there isn't a match, there's an error (#VALUE!) which then is handled by the ISNUMBER function.
    Then, those NOs and a YES are multiplied by the numbers from 1 to 4 (or whatever) to provide the index for the Department.

    Does that help?
    Last edited by kweaver; 2018-06-13 at 14:10.

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

    HowardC (2018-06-13)

  9. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,986
    Thanks
    156
    Thanked 779 Times in 709 Posts
    Hi Howard,

    While KW is the master of Excel formulas, I certainly cannot hold a candle. Here is a different approach using Microsoft's Regex in a UDF.

    First, I created a named range to include all the items in column H called "Dept". In my sample this was range H2:H5.

    Next, I added a reference to Microsoft VBScript Regular Expressions 5.5 from within the VB Editor. Tools > References... > Scroll down and tick the box next to "Microsoft VBScript Regular Expressions 5.5" > OK

    Add the following code to a standard module:
    Code:
    Public Function GETDEPT(rng As Range, department As Range)
    '------------------------------------
    'DECLARE AND SET VARIABLES
    Dim cell As Range
    Dim x As New RegExp
    '------------------------------------
    'CYCLE THROUGH EACH CELL IN COLUMN H
    For Each cell In department
    '------------------------------------
    'DETERMINE POSITION OF MATCHING ITEM IN STRING AND SET PATTERN
        If Left(rng, Len(cell)) = cell Then
            x.Pattern = cell & " "
        ElseIf Right(rng, Len(cell)) = cell Then
            x.Pattern = " " & cell
        Else: x.Pattern = " " & cell & " "
        End If
    '------------------------------------
    'TEST PATTERN AGAINST STRING AND ASSIGN DEPARTMENT
        If x.Test(rng) Then
            GETDEPT = Cells(cell.Row, "I")
        End If
    Next cell
    '------------------------------------
    'CLEANUP
    Set x = Nothing
    Set cell = Nothing
    End Function
    In cell B2, enter the following formula:

    =GETDEPT(A2,Dept) then copy down

    Adjust the "Dept" named range if additional items are added.

    HTH,
    Maud

    HC1.png

  10. The Following User Says Thank You to Maudibe For This Useful Post:

    HowardC (2018-06-13)

  11. #8
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,544
    Thanks
    145
    Thanked 7 Times in 7 Posts
    Thanks for your explanation and help Kweaver

  12. #9
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,544
    Thanks
    145
    Thanked 7 Times in 7 Posts
    If I change the Dept the UDF does not update automatically, even when pressing F9. I have to select the cell and press F2 and enter before it changes


    How do I overcome this ?

  13. #10
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,986
    Thanks
    156
    Thanked 779 Times in 709 Posts
    Hi Howard,

    Thanks for the thanks. Add Application.Volatile as the first line of code after the function declaration.

    Maud

  14. #11
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,544
    Thanks
    145
    Thanked 7 Times in 7 Posts
    Thanks Maud. It now updates immediately a change has been made

  15. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,367
    Thanks
    3
    Thanked 229 Times in 209 Posts
    FWIW, if you pass both the lookup and return ranges as arguments to the function, you wouldn't have to make it volatile, which is best avoided when possible.
    Regards,
    Rory

Tags for this Thread

Posting Permissions

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