+ Reply to Thread
Results 1 to 9 of 9

Formula to tag list of Items with Grouping Number

  1. #1
    Registered User
    Join Date
    08-02-2013
    Location
    australia
    MS-Off Ver
    Excel 2003
    Posts
    14

    Formula to tag list of Items with Grouping Number

    I have a list of items that are all coded (1500 or more items)
    eg BEBSHD6 , FOAT122 , HWTD5 etc
    The first two letters of each code denotes what catagory or group the item belongs to
    I have another list which has the two letter codes of all the 34 different item groups
    eg BE , FO , HW etc
    In the column next to these two letter codes is a number (1 to 35)
    eg BE next to 24 FO next to 1 HW next to 12 etc

    I am trying to create a formula in excel that will look at the first two letters of the item codes
    Then find these first two letters in the other list (the two letter groups list)
    Then tell me what the number is next to that two letter code

    This number has to be displayed next to the item code on the first list

    I Have tried lookup using LOOKUP(LEFT(A2,2) etc jsut can't get it to work
    I tried LOOKUP(LEFT(A2,2) and then if it equals the two letter code show me the number next to it
    But it only works for seven looks (my beginner terminology)

    Any genius out there can you please help

    Thanks
    Rob
    Last edited by superxl; 08-02-2013 at 03:35 AM. Reason: title change

  2. #2
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,745

    Re: Help with this Formula PLEASE

    Hi,

    I may as well tell you before the inevitable post from a Moderator that you will have to change the title of your thread to something less generic to comply with the Forum Rules before anyone can help you.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,337

    Re: Formula to tag list of Items with Grouping Number

    try vlookup

    =vlookup(left(A2,2),C1:D100,2,false)
    false-> exact match
    true->appro. match
    check your table for leading trailing spaces
    eg.
    in your table FOspace
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  4. #4
    Registered User
    Join Date
    08-02-2013
    Location
    australia
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Formula to tag list of Items with Grouping Number

    Thanks VLADY

  5. #5
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,337

    Re: Formula to tag list of Items with Grouping Number

    Your welcome.
    If you have no further query regarding this thread you can mark it as solved.

    Select Thread Tools from the menu link above and mark this thread as SOLVED.

    Thanks.

  6. #6
    Registered User
    Join Date
    08-02-2013
    Location
    australia
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Formula to tag list of Items with Grouping Number

    VLADY

    C1:D100 Changes as I drag down the formula
    eg C2:D101 , C3:D102

    How to stop this ?

    Rob

  7. #7
    Forum Contributor
    Join Date
    07-30-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    121

    Re: Formula to tag list of Items with Grouping Number

    Change VLADY code to

    =vlookup(left(A2,2),$C$1:$D$100,2,false)

  8. #8
    Registered User
    Join Date
    08-02-2013
    Location
    australia
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Formula to tag list of Items with Grouping Number

    Thank you all
    Problem solved

  9. #9
    Forum Contributor
    Join Date
    07-30-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    121

    Re: Formula to tag list of Items with Grouping Number

    CLICK the STAR icon on the left side below their user name to say thank you to those who had helped you. Thanks.

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.
    To undo, select Thread Tools-> Mark thread as Unsolved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 6
    Last Post: 10-08-2009, 03:45 AM

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