+ Reply to Thread
Results 1 to 4 of 4

Lookup across multiple columns

  1. #1
    Registered User
    Join Date
    06-07-2013
    Location
    Devon, England
    MS-Off Ver
    Excel 2007
    Posts
    8

    Lookup across multiple columns

    Lookup mutiple columns - formula
    Hello
    I have a formula that i need help with. If you look at the attached spreadsheet, you'll see that in Column D there is a fairly involved lookup_for_checking_new_codes - 4 letter codes - not working (2).xlsxformula that someone did for me a while ago. The idea is that it is creating new customer/prospect codes from a four letter prefix, and then add on a 3 digit suffix, to create an unique code. This is what is meant to happen:

    1) The formula in Col D looks at the letters in Col C and uses that as the first part of the new code. [eg ABCD]
    2) The formula in Col D will then add 3 digits to the prefix to create the code. [eg. ABCD001]
    3) It is then meant to look in Col A at the list of existing codes and if it sees the code it has just created in Col A, it will automatically use the next consecutive number. [ABCD002]
    4) If ABCD002 has already been used as well, then the formula should choose ABCD003, and so on.
    5) In addition, Col D also needs to look at the codes it has already created further up column D to make sure it isn't duplicating a code it has just created, and if so then use the next consecutive number.

    The formula is working okay on step 5, but not step 3; ie. it is looking further up Col D, but not Col A. The red cells in Col E on the attachment show where the formula is not working correctly. (I did a simple IF= formula to check where the duplicates were - formula now deleted).

    Can you help me to sort out this formula in Col D so that it correctly looks up both Col A and Col D?

    Many thanks and sorry for such a long post, i can't think of a more concise way to describe it!

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,576

    Re: Lookup across multiple columns

    This one starts with 000 and is a varient of what you have

    =LEFT(C2,4)&TEXT((RIGHT("000"& MAX(IF((I$2:$I$9999=C2)*(1),H$2:$H$9999)),3)+COUNTIF($C$1:C1,C2)),"000")

    This one starts with 001
    =C2&TEXT(COUNTIF(A:A,C2&"*")+ COUNTIF($D$1:$D1,C2&"*")+1,"000")
    I put the first equation in D and 2nd in E (modified of course)
    Attached Files Attached Files
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    06-07-2013
    Location
    Devon, England
    MS-Off Ver
    Excel 2007
    Posts
    8

    Smile Re: Lookup across multiple columns

    Hello ChemistB
    Thanks so much for solving my query - it is exactly what i wanted, wish i'd found you before!
    I'd like to click the star to add to your reputation but there wasn't one on the thread as far as i can see - sorry i'm new to this so not 100% au fait. If you can tell me where to find it, i'll click it!
    Thanks
    Caradonguy

    ** have found star now, and clicked it **

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,576

    Re: Lookup across multiple columns

    Glad I could help. The star is next to the triangle at the lower left of every post in the border.

+ 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