+ Reply to Thread
Results 1 to 4 of 4

Help with Array and index Formula

  1. #1
    Registered User
    Join Date
    02-04-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    23

    Red face Help with Array and index Formula

    Hi
    I want to little help with this.

    I attached an example of my problem and if someone ca give me a working formula i will be really grateful.

    here is summary of the problem:

    A B C D E F
    1 P1 P2 S1 P3 P4 S2
    2 a b 26 c d 24
    3 c a 23 b d 21
    4 b c 21 a d 11



    I want to make array when C>=20 and F>=20 it should give players and their scores, result should only give
    A B C D E F
    6 P1 P2 S1 P3 P4 S2
    7 a b 26 c d 24
    8 c a 23 b d 21

    results from 4th row will not appear because F4 is not greater tan 20.

    No macro or vb please. Only formula for excel.

    Thank you very much

    Workbook1.xlsx
    Last edited by shayej; 04-26-2015 at 03:39 PM. Reason: Solved

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Help with Array and index Formula

    Use this array formula in A14 and pull it to the right and down

    =IFERROR(INDEX(A$2:A$4,SMALL(IF(($C$2:$C$4>=20)*($F$2:$F$4>=20),ROW($C$2:$F$4)-ROW($A$2)+1),ROWS(A$1:A1))),"")

    ***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER
    Last edited by AlKey; 04-26-2015 at 01:24 PM.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Registered User
    Join Date
    02-04-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Help with Array and index Formula

    Worked like magic. Thank you.

  4. #4
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Help with Array and index Formula

    You're welcome and thank you 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)

Similar Threads

  1. Sum with array formula in index
    By Matthys.Steyn in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-17-2013, 10:44 AM
  2. Replies: 2
    Last Post: 06-20-2012, 12:22 PM
  3. access particular index of array formula?
    By zxcvbs in forum Excel General
    Replies: 2
    Last Post: 12-07-2009, 05:25 PM
  4. Index array formula?
    By kdestef1 in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 04-11-2009, 10:56 AM
  5. SUM of INDEX as array formula
    By CJEJung in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-21-2008, 01:15 PM

Tags for this Thread

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