Using Index, Small, Row, Rows in Array... Stops working..

[arossijr]

Hey again all!! As always you guys/gals are the greatest!!
I found a neat formula in an array, Tested it as it was setup and works great. When i go to modify it I get 3 results and boom it stops dead well rather it returns a blank as instructed.

Table: Group
Header: Group
Entries: Fox, Bear, Bear, Bear, Moose, Fox, Moose

Table: Name
Header: Name
Entries: Doug, Adam, Susan, Alex, Cindy, Mary

These 2 Tables are side by side split by a column (Empty Space)
Table: Results2
Headers: Groups, Result
Entries in Groups: Fox,Fox,Bear,Bear,Bear,Moose,Moose

Array Formula in use: =IFERROR(INDEX($D$4:$D$10,SMALL(IF($B$4:$B$10=J4,ROW($D$4:$D$10)-MIN(ROW($D$4:$D$10))+1),ROWS($K$4:K4))),"")
(Note: I tried using ranges to see if I can over come the problem... Survey says X )

The Results return as Follows under Header: Result - Doug, Cindy, Alex
Doug and Cindy are correct, Alex is correct but is the 3rd entry that is in the Bear row... It skipped Adam, and Susan. Then never put any entries after Alex in row 3 of the Results2 table.

Where did I go wrong?

p.s. this is the formula that uses the table names:



=IFERROR(INDEX(Name,SMALL(IF(Group=J4,ROW(Name)-MIN(ROW(Name))+1),ROWS($N$4:N4))),"")

 

[Marcelo Branco]

Tony,

You are very welcome.

Sometimes using Tables and Structured References complicates things, but we almost always find a workaround

M.

 

[MAlhash]

Hello,

I have Similar problem. the first if formula before the OR is working greatly, however after or its not giving me the data which i need.
basically, I have department names in a column, each department have two values separately. for example, if the first value is zero and the second values is 1 for the debarment name, it should return department name. but if both values equal 0 it should not return the department name. so, the condition is if one of the values is =0 return department name, if both 0 do not return, if both numbers return.

for me, the formula is working like the following.
if value 1 contain number and value2 contain zero it will return department name, which is true. however, if value 1 is =0 and value 2 = number it will not return the depatment name, which is wrong. it should return.

=IFERROR(INDEX($C$17:$C$29,SMALL(IF($D$17:$D$29>0,ROW($D$17:$D$29)),ROWS(K$14:K20))-ROW($D$17:$D$17)+1), OR(IFERROR(INDEX($C$17:$C$29,SMALL(IF($E$17:$E$29>0,ROW($E$17:$E$29)),ROWS(K$14:K20))-ROW($E$17:$E$17)+1),"")))

 

[Fluff]

Please start a thread of your own for this question, rather than taking it on to a 5 year old post.
When you do, please provide some sample data.