Index Array returning 1 incorrect result

[somliving]

Good Morning,

I am trying to return multiple results based off 1 criteria. I have the following furmula:

=IFERROR(INDEX(Employees!$C$2:$D$10,SMALL(IF(Employees!$C$2:$C$10='Global Assumptions'!$Q$3,ROW(Employees!$C$2:$C$10)-MIN(ROW(Employees!$C:$C))+1),ROWS($A$1:$A1)),2),"")

The value in 'Global Assumptions'!$Q$3=200f

Employees!$C$2:$D$348 look as follows:[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"></colgroup><tbody>[TR]
[TD="class: xl78, width: 64"][TABLE="width: 500"]
<tbody>[TR]
[TD]Property[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]200f[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl77, width: 64"]Bradley Himmelstein[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]200f[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl77, width: 64"]Patrick Mason[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]200f[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl77, width: 64"]Rimantas Samis[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]278m[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl77, width: 64"]Helen Buckley[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]278m[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl77, width: 64"]Pedro Lopez[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]510m[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl77, width: 64"]James Perez[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]510m[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl77, width: 64"]Albert Zuppe[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]510m[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl77, width: 64"]Steven Boddie[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]510m[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl77, width: 64"]Margaret Borer[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD="class: xl78, width: 64"][/TD]
[/TR]
</tbody>[/TABLE]


With the formula:


=IFERROR(INDEX(Employees!$C$2:$D$10,SMALL(IF(Employees!$C$2:$C$10='Global Assumptions'!$Q$3,ROW(Employees!$C$2:$C$10)-MIN(ROW(Employees!$C:$C))+1),ROWS($A$1:$A1)),2),"")


I am currently getting the following results:

[TABLE="width: 136"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Patrick Mason[/TD]
[/TR]
[TR]
[TD]Rimantas Samis[/TD]
[/TR]
[TR]
[TD]Helen Buckley [TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl77, width: 64"][/TD]
[/TR]
</tbody>[/TABLE]



I am not expecting to get Helen Buckley back as a result because her property=278 M. I would be expecting to get back [TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl77, width: 64"]Bradley Himmelstein

[/TD]
[/TR]
</tbody>[/TABLE]
but I am not. Please help![/TD]
[/TR]
</tbody>[/TABLE]

 

[Jonmo1]

This is the Row# argument in your index.
ROW(Employees!$C$2:$C$10)-MIN(ROW(Employees!$C:$C))+1
And it's evaluating to an array of {2,3,4,etc}

But you're indexing
Employees!$C$2:$D$10

The 2nd row in C2:D10 is 3.
You're offset by 1 row.

You need that to be an array beginning with 1 {1,2,3,etc}


What was the function of MIN(ROW(Employees!$C:$C)) ??
Try this as the row argument in the index
ROW(Employees!$C$2:$C$10)-ROW(Employees!$C$2)+1

=IFERROR(INDEX(Employees!$C$2:$D$10,SMALL(IF(Employees!$C$2:$C$10='Global Assumptions'!$Q$3,ROW(Employees!$C$2:$C$10)-ROW(Employees!$C$2)+1),ROWS($A$1:$A1)),2),"")

 

[somliving]

That did the trick! Thank you!

 

[Jonmo1]

You're welcome.