Page 2 of 2 FirstFirst 12
Results 11 to 20 of 20

Thread: Broken VLookup

  1. #11
    Board Regular
    Join Date
    Mar 2016
    Posts
    622
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Broken VLookup

    I just skimmed through this and i believe what you need is this

    =if(A2="","",VLOOKUP(A2,VL_Data!A:I,9,FALSE))
    Last edited by Nine Zero; Jan 22nd, 2018 at 11:40 AM.

  2. #12
    Board Regular
    Join Date
    Oct 2011
    Posts
    4,252
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Broken VLookup

    That was my fault I got in a hurry and gave you a bad formula.
    Try this. Copy formula down as needed. If needed change ranges to match your data,

    Sheet1

     ABHI
    1Emp. Name  Supr.
    2Bart A  Mack
    3Bart A  Mack
    4Bart A  Mack
    5    
    6Kyle A  Mack
    7Kyle A  Mack
    8    
    9Dane B  Jerrey
    10Dane B  Jerrey
    11Dane B  Jerrey

    Spreadsheet Formulas
    CellFormula
    I2=IF($A2="","",VLOOKUP($A2,VL_Data!A:B,2,FALSE))


    VL_Data

     AB
    1Emp. NameSupr.
    2Bart AMack
    3Kyle AMack
    4Dane BJerrey


    Excel tables to the web >> Excel Jeanie HTML 4

  3. #13
    New Member
    Join Date
    Jan 2018
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Broken VLookup

    Okay so now when I copy this over and I change the A# based on my rows... it's only resulting in Mack. No other supervisor name is copying over.

  4. #14
    Board Regular
    Join Date
    Oct 2011
    Posts
    4,252
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Broken VLookup

    What is the actual range that you have for the employee name and Sup. name on sheet VL_Data?
    I'm assuming on sheet 1 that employee name is starts in A2 and Sup. in cell I2.
    What is the actual formula you entered that is only returning Mack?

  5. #15
    New Member
    Join Date
    Jan 2018
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Broken VLookup

    Formula is the one above that you gave me.

    Cell range is as shown, currently goes through row 40 but is subject to change based on hires/terminations.


  6. #16
    Board Regular
    Join Date
    Oct 2011
    Posts
    4,252
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Broken VLookup

    By any chance did you lock both the column and the row to A2 (should be $A2 and not $A$2)?

    See the example below, instead of using whole columns I made the VL_Data range $A$2:$B$100 (note this range must be locked in).

    I also listed all the formulas so you can see how the formula should change as it's copied down. The employee names from one sheet to the next must match exactly.

    Sheet1

     ABHI
    1Emp. Name  Supr.
    2Acree, Bart  Mack
    3Acree, Bart  Mack
    4Acree, Bart  Mack
    5    
    6Axe, Kyle  Mack
    7Axe, Kyle  Mack
    8    
    9Borgan, Dane  Jerry
    10Borgan, Dane  Jerry
    11Borgan, Dane  Jerry
    12    
    13Harris, Robert  Mack
    14    
    15Dutton, Justin T  Nick
    16Dutton, Justin T  Nick

    Spreadsheet Formulas
    CellFormula
    I2=IF($A2="","",VLOOKUP($A2,VL_Data!$A$2:$B$100,2,FALSE))
    I3=IF($A3="","",VLOOKUP($A3,VL_Data!$A$2:$B$100,2,FALSE))
    I4=IF($A4="","",VLOOKUP($A4,VL_Data!$A$2:$B$100,2,FALSE))
    I5=IF($A5="","",VLOOKUP($A5,VL_Data!$A$2:$B$100,2,FALSE))
    I6=IF($A6="","",VLOOKUP($A6,VL_Data!$A$2:$B$100,2,FALSE))
    I7=IF($A7="","",VLOOKUP($A7,VL_Data!$A$2:$B$100,2,FALSE))
    I8=IF($A8="","",VLOOKUP($A8,VL_Data!$A$2:$B$100,2,FALSE))
    I9=IF($A9="","",VLOOKUP($A9,VL_Data!$A$2:$B$100,2,FALSE))
    I10=IF($A10="","",VLOOKUP($A10,VL_Data!$A$2:$B$100,2,FALSE))
    I11=IF($A11="","",VLOOKUP($A11,VL_Data!$A$2:$B$100,2,FALSE))
    I12=IF($A12="","",VLOOKUP($A12,VL_Data!$A$2:$B$100,2,FALSE))
    I13=IF($A13="","",VLOOKUP($A13,VL_Data!$A$2:$B$100,2,FALSE))
    I14=IF($A14="","",VLOOKUP($A14,VL_Data!$A$2:$B$100,2,FALSE))
    I15=IF($A15="","",VLOOKUP($A15,VL_Data!$A$2:$B$100,2,FALSE))
    I16=IF($A16="","",VLOOKUP($A16,VL_Data!$A$2:$B$100,2,FALSE))


    VL_Data

     AB
    1Employee NameSupervisor Name
    2Acree, BartMack
    3Axe, KyleMack
    4Borgan, DaneJerry
    5Dutton, Justin TNick
    6Harris, RobertMack
    7  


    Excel tables to the web >> Excel Jeanie HTML 4

  7. #17
    New Member
    Join Date
    Jan 2018
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Broken VLookup

    Sick of me yet?

    Those formulas all work, the problem now is that they are all returning a result of Mack, regardless of what the supervisor name is on the VL_Data table.. which makes NO sense to me at all.

  8. #18
    Board Regular
    Join Date
    Oct 2011
    Posts
    4,252
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Broken VLookup

    OK, do this for me.
    On your sheet one copy the formula you have in cell I2 for "Acree, Brad". Then go down to "Borgan, Dane" name and copy the formula you have in column I for them and post both formulas here where I can see them. I need to see what rows each formula is referencing.

  9. #19
    New Member
    Join Date
    Jan 2018
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Broken VLookup

    Bart : =IF($A2="","",VLOOKUP($A2,VL_Data!A:B,2,FALSE))

    Dane: =IF($A4="","",VLOOKUP($A4,VL_Data!$A$2:$B$103,2,FALSE))

  10. #20
    Board Regular
    Join Date
    Oct 2011
    Posts
    4,252
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Broken VLookup

    Why the different range for VL_Data (VL_Data!A:B and then VL_Data!$A$2:$B$103)?

    Is Dane's name in cell A4 of your sheet 1?

    Change the formula for Bart which I assume is in cell I2 to:
    IF($A2="","",VLOOKUP($A2,VL_Data!$A$2:$B$103,2,FALSE))
    Then copy this formula down.

    Take a look at the formulas in my post #16 above this is how your formulas should be if your data starts in row 2 of sheet1.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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