Error Checking in Excel
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Array formula to return multiple people that meet multiple criteria

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

    Default Array formula to return multiple people that meet multiple criteria

    Hi I really need some help with this. Sorry I canít post the excel file here as it has private information.

    What I am trying to do is get all of the names of people that spend over 1,000/ month that work for a specific company.

    The names are in column a that need to be returned. The amount is column T and the company is column aq. All other info I do not need for this array query. Does anyone know how to write a formula for this ?

    Thank you so much,

  2. #2
    Board Regular
    Join Date
    Oct 2011
    Posts
    3,838
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Array formula to return multiple people that meet multiple criteria

    Maybe something like this.
    This is an array formula and must be entered with CTRL-SHIFT-ENTER.
    Drag formula down rows as needed.

    ABSTUAPAQ
    1Name Amount Company
    2Emp1 200 Com1
    3Emp2 1100 Com2
    4Emp3 565 Com3
    5Emp4 3000 Com4
    6Emp5 250 Com5
    7Emp6 2500 Com2
    8Emp7 125 Com7
    9Emp8 98 Com8
    10
    11
    12FindCom2
    13NamesEmp2
    14 Emp6
    15

    Spreadsheet Formulas
    CellFormula
    B13{=IFERROR(INDEX($A$2:$A$9,SMALL(IF($AQ$2:$AQ$9=$B$12,IF($T$2:$T$9>1000,ROW($A$2:$A$9)-ROW($A$2)+1)),ROWS($B$13:B13))),"")}
    Formula Array:
    Produce enclosing
    { } by entering
    formula with CTRL+SHIFT+ENTER!


    Excel tables to the web >> Excel Jeanie HTML 4

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

    Default Re: Array formula to return multiple people that meet multiple criteria

    Hmm doesnít seem to produce anything

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

    Default Re: Array formula to return multiple people that meet multiple criteria

    Did you enter the formula with CTRL-SHIFT-ENTER? Excel will put {} around the formula if done right.
    Is the example I gave close to what your data looks like.
    Also you will need to change ranges to match your actual data.

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

    Default Re: Array formula to return multiple people that meet multiple criteria

    I changed the range and that is exactly how my data looks it returns some of them but not all. Any idea why this would be. Thank you for all your help truly and blessing

  6. #6
    Board Regular
    Join Date
    Oct 2011
    Posts
    3,838
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Array formula to return multiple people that meet multiple criteria

    How about posting the formula you are using.
    Also, remove the IFERROR part of the formula to see what type of error you get.
    Code:
    INDEX($A$2:$A$9,SMALL(IF($AQ$2:$AQ$9=$B$12,IF($T$2:$T$9>1000,ROW($A$2:$A$9)-ROW($A$2)+1)),ROWS($B$13:B13)))

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

    Default Re: Array formula to return multiple people that meet multiple criteria

    =index(data!$a$2:data!$a$20000,small(if(data!$aq$2:$aq$20000=ďadvertisingĒ,if(data!$t$2:data!$t$20000>1000,row(data!$a2:data!$a$20000)-row(data!$a$2)+1)),rows($a$24:a24))))

    Error #num

  8. #8
    Board Regular
    Join Date
    Oct 2011
    Posts
    3,838
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Array formula to return multiple people that meet multiple criteria

    Not sure it works for me. See below using your formula.
    Things to check:
    Is advertising spelled the same way and no spaces before or after the word.
    Are all the values in column T numeric?

    Sheet2

    A
    1Emp2
    2Emp4
    3Emp5
    4

    Spreadsheet Formulas
    CellFormula
    A1{=IFERROR(INDEX(data!$A$2:$A$20000,SMALL(IF(data!$AQ$2:$AQ$20000="advertising",IF(data!$T$2:$T$20000>1000,ROW(data!$A$2:$A$20000)-ROW(data!$A$2)+1)),ROWS($A$1:A1))),"")}
    A2{=IFERROR(INDEX(data!$A$2:$A$20000,SMALL(IF(data!$AQ$2:$AQ$20000="advertising",IF(data!$T$2:$T$20000>1000,ROW(data!$A$2:$A$20000)-ROW(data!$A$2)+1)),ROWS($A$1:A2))),"")}
    A3{=IFERROR(INDEX(data!$A$2:$A$20000,SMALL(IF(data!$AQ$2:$AQ$20000="advertising",IF(data!$T$2:$T$20000>1000,ROW(data!$A$2:$A$20000)-ROW(data!$A$2)+1)),ROWS($A$1:A3))),"")}
    A4{=IFERROR(INDEX(data!$A$2:$A$20000,SMALL(IF(data!$AQ$2:$AQ$20000="advertising",IF(data!$T$2:$T$20000>1000,ROW(data!$A$2:$A$20000)-ROW(data!$A$2)+1)),ROWS($A$1:A4))),"")}
    Formula Array:
    Produce enclosing
    { } by entering
    formula with CTRL+SHIFT+ENTER!


    data

    ABCSTUVAPAQ
    1Name Amount Company
    2Emp1 200 Com1
    3Emp2 1100 advertising
    4Emp3 565 Com3
    5Emp4 3000 advertising
    6Emp5 2500 advertising
    7Emp6 2500 Com2
    8Emp7 125 advertising
    9Emp8 98 Com8


    Excel tables to the web >> Excel Jeanie HTML 4

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

    Default Re: Array formula to return multiple people that meet multiple criteria

    It looks like it is only giving me back odd numbers ( I have a count if formula to give me different names for same co) why would this be?

  10. #10
    Board Regular
    Join Date
    Oct 2011
    Posts
    3,838
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Array formula to return multiple people that meet multiple criteria

    I didn't notice this before but in your part of the formula for the ROW you have data!$a2, it should be data!$a$2.

    =index(data!$a$2:data!$a$20000,small(if(data!$aq$2:$aq$20000=“advertising”,if(data!$t$2:data!$t$20000>1000,row(data!$a$2:data!$a$20000)-row(data!$a$2)+1)),rows($a$24:a24))))

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
  •