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

arossijr · Jul 27, 2018

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 · Jul 27, 2018

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.

arossijr · Jul 27, 2018

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

Marcelo Branco · Jul 27, 2018

You are very welcome. Glad to help

M.

arossijr · Jul 27, 2018

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),"")

arossijr · Jul 27, 2018

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!!

Marcelo Branco · Jul 27, 2018

arossijr said:
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.

arossijr · Jul 27, 2018

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!!

Marcelo Branco · Jul 27, 2018

Say you have 3 tables, like below, whose names, respectively, are: Group, Name, Result

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A

[/TD]
[TD="bgcolor: #DCE6F1"]

B

[/TD]
[TD="bgcolor: #DCE6F1"]

C

[/TD]
[TD="bgcolor: #DCE6F1"]

D

[/TD]
[TD="bgcolor: #DCE6F1"]

E

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3

[/TD]
[TD][/TD]
[TD]

Group

[/TD]
[TD][/TD]
[TD]

Name

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4

[/TD]
[TD][/TD]
[TD]

Fox

[/TD]
[TD][/TD]
[TD]

Doug

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5

[/TD]
[TD][/TD]
[TD]

Bear

[/TD]
[TD][/TD]
[TD]

Adam

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6

[/TD]
[TD][/TD]
[TD]

Bear

[/TD]
[TD][/TD]
[TD]

Susan

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7

[/TD]
[TD][/TD]
[TD]

Bear

[/TD]
[TD][/TD]
[TD]

Alex

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8

[/TD]
[TD][/TD]
[TD]

Moose

[/TD]
[TD][/TD]
[TD]

Cindy

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9

[/TD]
[TD][/TD]
[TD]

Fox

[/TD]
[TD][/TD]
[TD]

Mary

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10

[/TD]
[TD][/TD]
[TD]

Moose

[/TD]
[TD][/TD]
[TD]

John

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

13

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

14

[/TD]
[TD][/TD]
[TD]

Group

[/TD]
[TD]

Result