+ Reply to Thread
Results 1 to 5 of 5

Extraction formula pulling 1 too many names not matching the constant value

  1. #1
    Forum Contributor
    Join Date
    05-22-2012
    Location
    Boise, Idaho
    MS-Off Ver
    Office 365
    Posts
    156

    Extraction formula pulling 1 too many names not matching the constant value

    I have a list of agents assigned to team leads. I need to have this list to be able to update as team changes happen month over month. The formula looks to be working except is add one too many name of agents to each of the teams that does not belong. I need help with index Extraction formula pulling 1 too many names (agents) not matching the constant value (team Lead). Here is the formula
    =IFERROR(INDEX($A$1:$A$150,SMALL(IF($B$1:$B$150=G$1,ROW($B$2:$B$150)-ROW($G$1)+1),ROWS($G$1:$G1))),"")

    what is happening is for each team it pulls one extra name at the bottom of the list that does not belong to that team of agents.

    see attached
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,978

    Re: Extraction formula pulling 1 too many names not matching the constant value

    Very small change!

    From this:

    =IFERROR(INDEX($A$1:$A$150,SMALL(IF($B$1:$B$150=G$1,ROW($B$2:$B$150)-ROW($G$1)+1),ROWS($G$1:$G1))),"")

    to this:

    =IFERROR(INDEX($A$1:$A$150,SMALL(IF($B$1:$B$150=G$1,ROW($B$2:$B$150)-ROW($G$1)),ROWS($G$1:$G1))),"")
    Attached Files Attached Files
    Last edited by AliGW; 03-22-2022 at 08:28 AM. Reason: Workbook attached.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Valued Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    897

    Re: Extraction formula pulling 1 too many names not matching the constant value

    Remove the '+1' from your formula and it should work.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If your Question is answered; please mark it SOLVED. If you are happy with a member's solution, say 'Thanks' and click the 'Star' to Add Reputation.

  4. #4
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,914

    Re: Extraction formula pulling 1 too many names not matching the constant value

    Try this in G2:

    =FILTER($A$2:$A$150;$B$2:$B$150=G$1)

  5. #5
    Forum Contributor
    Join Date
    05-22-2012
    Location
    Boise, Idaho
    MS-Off Ver
    Office 365
    Posts
    156

    Re: Extraction formula pulling 1 too many names not matching the constant value

    Thank you for your help-- solutions worked great

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 2
    Last Post: 12-19-2019, 12:03 AM
  2. Multiple Names Extraction Version 2?
    By wedzmer in forum Excel General
    Replies: 3
    Last Post: 02-01-2019, 03:46 AM
  3. Multiple NAMES extraction
    By wedzmer in forum Excel General
    Replies: 9
    Last Post: 01-31-2019, 10:49 PM
  4. [SOLVED] Click/drag a formula pulling from differ sheets w/ excel auto changing sheet names only
    By GonzCilla in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-20-2018, 02:16 PM
  5. Pulling out single names from a string of names
    By HBF in forum Excel General
    Replies: 4
    Last Post: 02-23-2013, 08:25 AM
  6. Replies: 2
    Last Post: 05-27-2010, 10:09 AM
  7. automate single cell data extraction and matching...
    By soundchaser77 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-24-2009, 10:19 PM

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