Forum Discussion

DCBanks's avatar
DCBanks
Copper Contributor
Nov 17, 2019
Solved

INDEX, SMALL, COUNTIF

I am using INDEX, SMALL, COUNTIF to extract records from a list with 10 columns and averages 200 rows. There's is a 2 text analysis code and a 3 text budget code, examples are AD, EQ, ST for analysis...
excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Nov 17, 2019

    DCBanks 

    If to extract by formulas records from list based on few criteria, I'd use AGGREGATE to avoid array formulas.

    In this case

    formula could be like

    =IFERROR(
   INDEX(A$5:A$38,
      AGGREGATE(
         15,
         6,
         1/(**bleep**=$O$10)/(Budget=$P$10)/(ProjectCode=$Q$10)*
            (ROW($A$5:$A$38)-ROW($A$4)),
         (ROW()-ROW($O$10))
      )
   ),
"")

    and in 1/()/().. part you may add as many criteria as needed.

DCBanks's avatar
DCBanks
Copper Contributor
Nov 17, 2019

Riny_van_Eekelen 

I agree I can do that and have tried it out on the users who have knowledge of Excel ranging from nothing to nought! The users numbering 40 plus ring me up and tell me they are missing transactions and I ask them they can see little arrows at the top of the columns and they ask me 'what is a column'! Entering 3 cells is about all they can manage.

brawo786's avatar
brawo786
Copper Contributor
Nov 23, 2024

IF(ROWS(H$6:H6)>COUNTIFS($B$2:$B$12,$I$1,$C$2:$C$12,$K$1,$D$2:$D$12,$M$1),"",INDEX(E$2:E$12,SMALL(IF($I$1=$B$2:$B$12,$K$1=$C$2:$C$12,$M$1=$D$2:$D$12,ROW(E$2:E$12)-ROW(E$1)ROWS(H$6:H6)))) kindly help about this formula i want to find employee data with three conditions

1- Campus wise

2- faculty wise

3- department wise

find employee in ms-office 2010

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Nov 23, 2024

    I'd recommend to start new discussion with this question giving more details about the data, screenshot at least

Resources