Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Apr 2009
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello, I am familiar with Vlookups but have not worked with them in a while. Here is the scenario: I have color codes and want a cell to give me the correct color description for the color code so:

    BKL or BK = Black

    The array where the information is looked up is called "ColorCode" and it looks like the following:
    A2 A3 A4
    BKL BK Black
    LIL LC Lilac

    and etc. There are about 45 rows of colors.

    Here is the thing: When I have one color I want to look up, it works perfectly, when I have two colors combined as follows:
    BKLC, which would be "black lilac", I don't know how to get Excel to look in the second column (A3) for both of the values and give me a two color result.

    Where I want the informaiton is on the first sheet called "Main", so it would look something like this:

    E2 F2 G2
    Size Color Color Description
    06 BKLC

    Is it possible to have one formula that will look for a single value like "BLK" (black) and then also look for the combined colors BKLC? Would it perhaps be a Vlookup within a Vlookup? I would be very grateful for a solution to this problem.

    Tina

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I would make the formula simpler with one lookup and make the lookup table larger.
    Only 2 columns: the code and the Color
    A B
    BK Black
    BLK Black
    LIL Lilac
    LC Lilac
    ....
    BKLC Black-Lilac


    Etc

    This makes more sense to me than trying to to create a formula to parse and do multiple lookups

    Steve

  3. #3
    Lounger
    Join Date
    Apr 2009
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='sdckapr' post='770639' date='15-Apr-2009 11:58']I would make the formula simpler with one lookup and make the lookup table larger.
    Only 2 columns: the code and the Color
    A B
    BK Black
    BLK Black
    LIL Lilac
    LC Lilac
    ....
    BKLC Black-Lilac


    Etc

    This makes more sense to me than trying to to create a formula to parse and do multiple lookups

    Steve[/quote]

    Steve, you are probably right. I actually did that after I posted this question. Thanks for your input.

    Tina

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If you are consistent in your setup (Col a is 3 chars, Col B is 2 chars, combined colors are 4 chars (2+2)), you could have your 45 row table and use something like:

    =IF(LEN(E2)=2,VLOOKUP(E2,$B$2:$C$46,2,0),IF(LEN(E2 )=3,VLOOKUP(E2,$A$2:$C$46,3,0),IF(LEN(E2)=4,VLOOKU P(LEFT
    (E2,2),$B$2:$C$46,2,0)&" "&VLOOKUP(RIGHT(E2,2),$B$2:$C$46,2,0),"Don't Know")))

    So if the length is 2 it uses col B, if the length is 3 column A, if 4 it does 2 lookups on col B using the left2 and the right 2 chars.

    Steve

Posting Permissions

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