Index formula issue

0
votes

image

Can you please help with the above? Basically, i am trying to automate a list of names based on another persons name within an organization. I.e. John Smith being a manager, I want to return every person who is under John Smith. So, my formula is as below:

=IFERROR(INDEX(Sheet1!B:B,SMALL(IF(Sheet1!A:A=$AF$34,ROW(Sheet1!B:B)-MIN(ROW(Sheet1!B:B))+1),COLUMNS($AF$34:AF34))),"")

Sheet1!B:B is where the employees are and Sheet1!A:A is where the managers are. When i press control shift and enter, that works and returns the correct value. The problem is however, say I have 10 people under John Smith and the next manager is Dave T, the list will carry on and auto populate people under Dave when i want it to return the "" value provided in the IFERROR (presuming they are not under John Smith). To do this, i am adding +1 to:

...(Sheet1!B:B))+1),COLUMNS...

i.e.

...(Sheet1!B:B))+2),COLUMNS... ...(Sheet1!B:B))+3),COLUMNS...

This is driving me insane, can you please help?

Thanks, Colin.

2
excelexcel-formula
Are you dragging horizontally? - Glitch_Doctor
Im dragging vertically as in the example above, is that the problem? - colin
You are asking for the count of columns as the argument for which row to return with small, if you are dragging vertically then you want ROWS() or ROW(1:1) - Glitch_Doctor
Thank you, would ROW(1:1) be static or like ROW(AF40:AF40)? Also, could you please post an example of the formula which you would believe to be correct? - Many thanks! - colin
Small returns the nth smallest result. You want to use ROW(1:1) so that you get the first match but it will progress to the 2nd 3rd etc. - Glitch_Doctor

2 Answers

0
votes

Firstly, if at all possible it is best to avoid using entire columns for INDEX formulas for performance reasons, but if this is your only formula you should be fine.

Now as you are indexing the entire column, you shouldn't need to offset the row result (and in my example i start the index from row 1 so again no need to offset). In addition, you want to have the small update one step at a time, this is most commonly done with ROW(1:1) [Vertical dragging] or COLUMN(A:A) [horizontal dragging] your method also works but people are lazy and like to save characters:

=IFERROR(INDEX(Sheet1!B:B,SMALL(IF(Sheet1!A:A=$AF$34,ROW(Sheet1!B:B)

-MIN(ROW(Sheet1!B:B))+1),COLUMNS($AF$34:AF34))),"")

Essentially your formula will work for dragging horizontally but not for vertical dragging, it will always yield the first row number in the array...

=IFERROR(INDEX($B$1:$B$11,SMALL(IF($A$1:$A$11=$AF$34,ROW($B$1:$B$11)),ROW(1:1))),"")

Ctrl + Shift + Enter (formula bar)

enter image description here

0
votes

Say we have:

enter image description here

In D2 enter the mane of the manager of interest. In C2 enter:

=IF($D$2=A2,1+MAX($C$1:C1),"")

and copy down:

enter image description here

The Helper column marks each subordinate with a unique seq. number.

Finally, in E2 enter:

=IFERROR(INDEX(B:B,MATCH(ROWS($1:1),C:C,0)),"")

and copy down:

enter image description here

NOTE:

By changing a single cell, D2, we can pick a different manager.