Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: Broken VLookup

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

    Default Broken VLookup

    I have a spreadsheet where the data is on 2 tabs and the vlookup has been broken. The person in this position before me didn't bother to fix it and now I'm having trouble doing so. Is anyone willing to help out? I think an INDEX MATCH is better in this case and I've been trying to do one but I can't get it to work either. Thank you!

  2. #2
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,647
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Broken VLookup

    Hi. What does broken mean? It would be helpful to see the formula and what you think is broken about it.

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

    Default Re: Broken VLookup

    Unfortunately I can't tell you the answer to that. The previous girl had deleted the formula and told me it was broken. I got one Index to work but couldn't get the match to work. I would rather not do 70 different index formulas, I think either an index match or vlookup would be easier. I have screenshots of the spreadsheet and the current NA error on the match. The idea is that it matches the employee name on the first tab to the employee and supervisor name on the 2nd tab and then fills in the supervisor name column on the first tab (the yellow boxes)

    https://ibb.co/dx9bzG
    https://ibb.co/neRZmw
    https://ibb.co/iTAieG

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

    Default Re: Broken VLookup

    See if this example helps.
    Copy formula down as needed.

    VLOOKUP will not work the way your table is set up on sheet 2. VLOOKUP can't look to the left, so you would need the table set up with Emp. Name and then Sup. Name.

    Sheet1

     ABGHI
    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="", "",INDEX(Sheet2!$A$2:$A$4,MATCH($A2,Sheet2!$B$2:$B$4,0)))


    Sheet2

     AB
    1Sup. NameEmpy. Name
    2MackBart A
    3MackKyle A
    4JerreyDane B


    Excel tables to the web >> Excel Jeanie HTML 4

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

    Default Re: Broken VLookup

    Thank you, that helped for the first employee but all others are giving me an N/A Error. I'm pretty determined to get this fixed LOL

    I moved the columns on the 2nd sheet. Can you tell me what is wrong with my vlookup? Employee Name is now Column A and Supervisor Name is Column B on the VL_Data sheet


  6. #6
    New Member
    Join Date
    Oct 2012
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Broken VLookup

    kscwgirl,

    Give this a try...

    =IFERROR(INDEX(VL_Data!B:B,MATCH($A2,VL_Data!A:A,0)), "NoMatch")
    =INDEX ( Column I want a return value from , MATCH ( My Lookup Value , Column I want to Lookup against , Enter “0” ))



    Don

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

    Default Re: Broken VLookup

    That's giving me a NAME? error.

  8. #8
    New Member
    Join Date
    Oct 2012
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Broken VLookup

    kscwgirl,

    Did you remember to put the ! at the end of your VL_Data sheet name in the formula? So it should be VL_Data!

    Looking at the picture of your data in Sheet1 and your explanation that the VL_Data! sheet has employee names in A:A and supervisor name in column B, this formula should work. Does employee name in Sheet1 match the name in VL_Data! exactly, if not you would be getting a No Match error tho'. Works on my sample worksheets....

    Put formula in Sheet1 I2 and copy down
    =IFERROR(INDEX(VL_Data!B:B,MATCH($A2,VL_Data!A:A,0)), "NoMatch")

    Explanation of Index
    =INDEX ( Column I want a return value from , MATCH ( My Lookup Value , Column I want to Lookup against , Enter “0” ))

    Don

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

    Default Re: Broken VLookup

    Change your lookup to:
    =if(A2="","",VLOOKUP(A2,VL_Data!A:A,VL_Data!I:I,FALSE).
    Copy down.

    It is best not to reference whole columns, and reference only the range that holds your data.
    The first argument in VLOOKUP(Lookup value) can only reference one cell not a range.
    See this link:
    https://support.office.com/en-us/art...rs=en-US&ad=US

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

    Default Re: Broken VLookup

    I have never thought of myself as being bad at excel until this spreadsheet. It's enough to make me want to throw things.

    I copied that formula above and this is what I get. I promise I am not normally this dense. Just something about vlookups I do not get.


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
  •