Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Through the use of a VLOOKUP AND IF statement, I am trying to record the data from the MASTER worksheet onto the ALLOCATION w/s. I have annexed a workbook that has three (3) worksheets. 1> MASTER, 2> ALLOCATION WITH VLOOKUP FORMULA & 3> ALLOCATION GOAL. The last worksheet illustrates how I want the worksheet to properly look .

    The ALLOCATON WITH VLOOKUP FORMULA is to test the EEID for each row to the EEID in the MASTER w/s. If that matches then the formula is to look at the MASTER column REPEATED EEID,(for the same EEID )and determine if the cell contains a 0 or a 1. If a 0 then it will populate the cells in columns %_1, C#1, TITLE'#1 for the respective EEID.
    If a 1 is recorded in the REPEATED EEID,(for the same EEID ), then it will populate the cells in columns %_2, C#2, TITLE'#2 for the respective EEID.

    I cannot get the formula to work in two ways. First I get #N/A codes in some of the cells and secondly the VLOOKUP is picking up the first set of data for the respective EEID on the MASTER w/s.

    Thank you for your assistance.
    Attached Files Attached Files

  2. #2
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts
    After a review of my posted Thread, I noticed that the example workbook was incorrect in the allocation worksheets. I am attaching the corrected work book on this reply.

    Sorry. But I still need some assistance.

    Thank you.
    Attached Files Attached Files

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    MNN,

    I've worked on this for a while and keep getting #REF errors when I know the formulas should work!
    One thing I did notice is that you have the formulas set as ARRAY formulas. You don't really want them here.

    I broke the formula down into component parts and the only one I could get to work was the =if(Master!B2=0,True,False)
    All the attempts at using Vlookup returned #REF errors when a visual inspection of the formula says it is correct.

    Another thing I noticed and fixed in my test copy was that some of the values in the last 2 columns were numbers and some were formatted as text via a leading apostrophe ('). Still no love!

    Sorry I can't be of more help but I fear something is seriously messed in this workbook.

    RG
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thanks for the attempt. Not exactly sure what you meant in the first line saying I tried an array and the formula should be broken down?

    To simplify the project, I am trying to have the allocation from the MASTER w/s as one row on the ALLOCATION w/s for each EEID.

    the second workbook attachment is the correct one. Please disregard the first workbook.

  5. #5
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    The attached Might resolve the issue

    [attachment=88286:VLOOKUP ON SECOND ITEM WITH SAME ID #-CORRECTED _AW.xls]


    But to Clarify a few bits

    1. The first row had an array formula in it
      This is a formula added with SHIFT CTRL ENTER
      It is a special type of formula and was not a problem here just that the
      underlying formula was NOT correct.
    2. The #N/A was primarily caused by the LOOKUP Range starting in A13 on the master rather than A2
    3. The idea behind IF and VLOOKUP was OK, but in reality that is NOT how VLOOKUP works.
      It picks up the first (and ONLY the first Match in 1st Column (A in this case)
      To Pick up the 1's and the 0's you need to create a UNIQUE Reference Column on the Master.
      I did this concatenating the Code to a 0 or 1
      e.g A2 = B2 & "_" & C2
      I added the _ to cleary show difference from just number
      With a Unique Column I could then use the VLOOKUP without the IF
      VLOOKUP($A2&"_"&0,MASTER!$A$2:$F$26,4,FALSE)

      However, IF you do not want the #N/A to show up when there is NO Data
      Then you can use ISNA as I did

      =IF(ISNA(VLOOKUP($A2&"_"&0,MASTER!$A$2:$F$26,4,FAL SE)),"",(VLOOKUP($A2&"_"&0,MASTER!$A$2:$F$26,4,FAL SE)))


    Hope that makes sense.
    Attached Files Attached Files
    Andrew

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    AKW,

    Very Nicely Done!

    RG
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Looks like a Winner. Many Thanks. I'll get back to you after I put this into action.

Posting Permissions

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