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

arossijr

Board Regular
Joined
Aug 29, 2016
Messages
72
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))),"")
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Try this formula in K4 copied down
=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),COUNTIF(J$4:J4,J4))),"")
Ctrl+Shift+Enter

M.
 
Upvote 0
Marcelo, You Rock!! that worked like a charm!!! I am going to see if i can make with work with Table Entries...

I have been struggling with 4 different variants of Formulas to do what yours just did! Never though to add a countif in there!!

Thanks again!
Tony
 
Upvote 0
Marcelo,

I broke it trying to use the table names...Now ive done gone and done it, So now the results Equal the First Returnable Answer almost like a Vlookup!
I tried using this: =IFERROR(INDEX(Name,SMALL(IF(Group[Group]=M4,ROW(Name)-MIN(ROW(Name))+1),COUNTIF([@Groups],M4)),1),"")
 
Upvote 0
Marcelo and Company,

I think I located the Problem... It is the Range Setting in the CountIf...
If I use this Formula (Marcelo's from above) =IFERROR(INDEX(Name[Name],SMALL(IF(Group[Group]=M4,ROW(Name[Name])-MIN(ROW(Name[Name]))+1),COUNTIF(M$4:M4,M4))),"")

The issue is trying to use Table Names and Headers in the CountIf part. If i use the Range it works fantastically!!
 
Upvote 0
Marcelo and Company,

I think I located the Problem... It is the Range Setting in the CountIf...
If I use this Formula (Marcelo's from above) =IFERROR(INDEX(Name[Name],SMALL(IF(Group[Group]=M4,ROW(Name[Name])-MIN(ROW(Name[Name]))+1),COUNTIF(M$4:M4,M4))),"")

The issue is trying to use Table Names and Headers in the CountIf part. If i use the Range it works fantastically!!

Great! Glad it's working.

M.
 
Upvote 0
Marcelo,

Is there a way to turn Countif(M$4:M4,M4))) into something that uses the Table Headers or Table? Or am I beating my head on a rock for nothing? LOL
Thanks again, I used the other formula that you posted as well earlier and that works just as well... but same issue with Countif range being by cell not table...LOL But I think I need to pick a new Rock ...

Thanks again! Have a Fantastic Weekend!! Hope all is well in Brazil!!
 
Last edited:
Upvote 0
Say you have 3 tables, like below, whose names, respectively, are: Group, Name, Result


A
B
C
D
E
1
2
3
Group​
Name​
4
Fox​
Doug​
5
Bear​
Adam​
6
Bear​
Susan​
7
Bear​
Alex​
8
Moose​
Cindy​
9
Fox​
Mary​
10
Moose​
John​
11
12
13
14
Group​
Result​
15
Moose​
Cindy​
16
Moose​
John​
17
Bear​
Adam​
18
Bear​
Susan​
19
Fox​
Doug​
20
Fox​
Mary​
21
Bear​
Alex​
22

<tbody>
</tbody>


Array formula in C15
=INDEX(Name[Name],SMALL(IF(Group[Group]=B15,ROW(Group[Group])-MIN(ROW(Group[Group]))+1),COUNTIF(INDEX([Group],1):[@Group],[@Group])))
Ctrl+Shift+Enter

M.
 
Upvote 0
Marcelo,

Right on the Head of the nail!! Thanks again!!! Greatly Greatly Appreciated. Now I can release that poor rock back into the wild.

Thank you!!!
Tony
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top