+ Reply to Thread
Results 1 to 7 of 7

Return a summary of multiple vlookup results

  1. #1
    Registered User
    Join Date
    02-21-2019
    Location
    London
    MS-Off Ver
    2013
    Posts
    5

    Return a summary of multiple vlookup results

    Table1 has a list of incidents. Table2 has a list of actions and whether they are 'In progress' or 'complete'. Some incidents have no actions and some incidents have 3 actions, the status of each action is in table2.

    I want table1 to show if all the actions are completed for each incident. Vlookup only returns the status of the first action it finds. I'd like it to find the 3 actions and report if they are all complete. I'm happy to have helper columns. I want it to be live rather than have to click a button.

    I've spent a couple of hours on this and can't crack it. any ideas? file attached

    Thanks
    John
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    365
    Posts
    5,371

    Re: Return a summary of multiple vlookup results

    If you change table 1 to

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    3
    Table 1 - Incidents
    4
    Incident number
    Complete
    In progress
    5
    1
    1
    1
    6
    2
    2
    1
    7
    3
    0
    0
    8
    4
    0
    0
    9
    5
    0
    0
    Sheet: Sheet1

    Formula in C5 copied down & across
    =COUNTIFS($B$13:$B$17,$B5,$D$13:$D$17,C$4)

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    22,634

    Re: Return a summary of multiple vlookup results

    If that's not it... tell us what your expected answers are....
    Glenn



  4. #4
    Registered User
    Join Date
    02-21-2019
    Location
    London
    MS-Off Ver
    2013
    Posts
    5

    Re: Return a summary of multiple vlookup results

    Thank you, this works nicely. Just kept the 'In progress' column and used conditional formatting for colours.

    Is there a way for the formula to write 'No Actions' if it can't find a row with an incident number in Table2? when using vlookup i just wrapped with a 'IF' function but that won't work here.

    Thanks
    John

  5. #5
    Forum Expert
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    365
    Posts
    5,371

    Re: Return a summary of multiple vlookup results

    How about
    =IF(COUNTIFS($B$13:$B$17,$B5,$D$13:$D$17,C$4)=0,"No Action",COUNTIFS($B$13:$B$17,$B5,$D$13:$D$17,C$4))

  6. #6
    Registered User
    Join Date
    02-21-2019
    Location
    London
    MS-Off Ver
    2013
    Posts
    5

    Re: Return a summary of multiple vlookup results

    Thank you! brilliant support from Fluff13 and this forum!

  7. #7
    Forum Expert
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    365
    Posts
    5,371

    Re: Return a summary of multiple vlookup results

    You're welcome & thanks for the feedback

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1