Results 1 to 6 of 6

Thread: VLOOKUP Fails

  1. #1
    New Member
    Join Date
    Feb 2019
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VLOOKUP Fails

    I've built a timesheet with 15-minute increments. Users enter their task and what project the task is part of, and a single project can have multiple tasks. All projects are in one column and all tasks are in a neighboring column. Total time is listed beside them. The range is D2:G166 (NOTE: Column F is a notes column and has no value for the purposes of the summary table). The sheet is named "Detail". See example below:

    D E G
    Task
    Project
    Hours
    Task 1 Project 1
    Task 1 Project 1 .5
    Task 2 Project 2 .25
    Task 3 Project 1 .25
    Task 2 Project 2 .25
    Task 4 Project 3
    Task 4 Project 3
    Task 4 Project 3 .75
    I've been using the timesheet for nearly a year now and it works fine, but I've decided it's time to create a summary page for it on a different sheet. My first step was to create a list of unique tasks using an array formula:

    Code:
    {=IFERROR(INDEX(Detail!$D$2:$D$166,MATCH(0,COUNTIF($A$1:A1,Detail!$D$2:$D$166),0)),"")}
    This formula was then drag-copied down over the range A2:A167 and works fine.

    Next, I created an Hours column that determines how much time is paid to each individual task, regardless of where it appears in the original table:

    Code:
    =IF(OR(A2="",A2=0),"",SUMIF(Detail!$D$2:$D$166,A2,Detail!$G$2:$G$166))
    The formula uses the result of the previous formula as the criteria for the SUMIF to locate the appropriate numbers to sum up. This too works just fine across the range C2:C167.

    Now we get to the problem. In column B, I created a formula that would determine what project each task was assigned to:

    Code:
    =IFERROR(VLOOKUP(A2,Detail!$D$2:$E$166,2),"")
    Like the previous formula for summing hours, this one is referencing the result of the first formula as the VLOOKUP lookup value. But instead of doing that, it targets seemingly random tasks. I can't figure out why it would do that, but I've determined that all values are correct as I understand VLOOKUP—and I should understand it, as I use it frequently. Is it because the first formula is an array formula? If that's a problem, why does the SUMIF formula work fine when the VLOOKUP doesn't? I am thoroughly confused and can't seem to locate any information on this particular problem in my online searches, so here we are.

    The final summary table is meant to look like the below (using the example table above as a reference):

    A B C
    Task
    Project
    Hours
    Task 1 Project 1 .5
    Task 2 Project 2 .5
    Task 3 Project 1 .25
    Task 4 Project 3 .75

  2. #2
    Board Regular
    Join Date
    Oct 2014
    Location
    UK
    Posts
    3,826
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VLOOKUP Fails

    the extra ,0 forced an exact match

    =IFERROR(VLOOKUP(A2,Detail!$D$2:$E$166,2,0),"")

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,203
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VLOOKUP Fails

    If your list isn't sorted, you need to specify an exact match
    =IFERROR(VLOOKUP(A2,Detail!$D$2:$E$166,2,0),"")
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    New Member
    Join Date
    Feb 2019
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VLOOKUP Fails

    That fixed it! Now I feel silly for neglecting it. Thanks!

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,203
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VLOOKUP Fails

    Glad we could help & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #6
    Board Regular
    Join Date
    Oct 2014
    Location
    UK
    Posts
    3,826
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VLOOKUP Fails

    no worry.
    we all done it before

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
  •