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:

Property Name 200f Bradley Himmelstein <tbody> </tbody> 200f Patrick Mason <tbody> </tbody> 200f Rimantas Samis <tbody> </tbody> 278m Helen Buckley <tbody> </tbody> 278m Pedro Lopez <tbody> </tbody> 510m James Perez <tbody> </tbody> 510m Albert Zuppe <tbody> </tbody> 510m Steven Boddie <tbody> </tbody> 510m Margaret Borer <tbody> </tbody> <tbody> </tbody>

<colgroup><col width="64" span="2" style="width:48pt"></colgroup><tbody>
</tbody>

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:

Patrick Mason

Rimantas Samis

Helen Buckley <tbody> </tbody> I am not expecting to get Helen Buckley back as a result because her property=278 M. I would be expecting to get back Bradley Himmelstein <tbody> </tbody> but I am not. Please help!

<colgroup><col></colgroup><tbody>
</tbody>

 

[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.