Index function not working in the lower area of a spreadsheet

[dlhtox]

Need help with an index lookup using this formula:


=IFERROR(INDEX(Component,SMALL(IF(Component[OWNER]=A$2,ROW(Component[OWNER])),ROW(1:1))-1,1),"")


Component is a range I have created that has two columns: Owner and then a list of Components that Owner works with.


I point to a single cell that has the name of an Owner and have it parse the range and return a component that owners works with.


The next line contains an update to the formula that returns the subsequent lookup for the next Component still based on the original name from the fixed cell.


=IFERROR(INDEX(Component,SMALL(IF(Component[OWNER]=A$2,ROW(Component[OWNER])),ROW(2:2))-1,1),"")


This works through my spreadsheet but just does not work further down in the spreadsheet, really I cannot find a rhyme or reason why.

So what my spreadsheet looks like is this.

John Trafelet

Agent Health
Agent Install
Embedded Agent
Linux & Unix

And this comes from a list that looks like this:

[TABLE="width: 314"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Agent Health[/TD]
[TD]John Trafelet[/TD]
[/TR]
[TR]
[TD]Agent Install[/TD]
[TD]John Trafelet[/TD]
[/TR]
[TR]
[TD]Alerting[/TD]
[TD]Will Coffey[/TD]
[/TR]
[TR]
[TD]ALM / LPM[/TD]
[TD]Jon Bartholomew[/TD]
[/TR]
[TR]
[TD]AMT/vPro[/TD]
[TD]Aaron Day[/TD]
[/TR]
</tbody>[/TABLE]

More of John Trafelet's responsibilities are further down the list... and that is what populates "Embedded Agent" and "Linux and Unix"

I have a list of items that John works with in a list with other peoples names.
I want people to be able to look up what items John works with, however John's responsiblities may change and I want to simply be able to put another name in the list and have it auto-update.

This all works fine, however lower down in the spreadsheet my formula although formatted correctly returns nothing.

 

[Chrisdontm]

It may be that the range you created named: " Component " is limiting you.

Is it a dynamic range, ( is the range growing ? )

Hold the Ctrl. key and hit the ' F3 " key and see if the range of "component" goes on down the page or not...
( Otherwise I would have to see the spreadsheet )