Forum Discussion
INDEX, SMALL, COUNTIF
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.
It's an amazingly complicated formula. What exactly are you trying to achieve?
Have you considered putting a filter on your column headers? It seems you can get the same extract by just ticking the relevant check boxes in the column filters. E.g., you can select three Analysis codes, a Budget code and a Project code and then it will display all rows that match that filter. That's roughly what your formula is doing now for only one Analysis code.
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.
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
I'd recommend to start new discussion with this question giving more details about the data, screenshot at least
