Help with an Array formula

[Craig4670]

Hello:

I have an array formula that I am trying to modify and I can't get it to work correctly. I'm not sure what I am doing wrong.
I have a scroll bar on a previous worksheet that will enable the user to select if a project is completed or not.

Example:

On Going Activities (Cell B2)
Status (Cell B3)	Task (Cell C3)	Step (D3)
Completed	Receive Drawings from Engineering	1.1
Completed	Meet with planning dept for build schedule	1.2
Completed	Determine which suppliers to quote package	1.3
Completed	Prepare quotation package to send to suppliers	2.1

<tbody>
</tbody>

Cell B32 is for the scroll bar.

Completed (B32)	Determine which suppliers to quote package (C32)	1.3 (D32)
	Prepare quotation package to send to suppliers (C33)	2.1 (D33)

<tbody>
</tbody>

The array formula that I am using for C32 is - {=IF(ISERROR(INDEX($B$2:$C$26,SMALL(IF($B$2:$B$26=$B$32,ROW($B$2:$B$26)),ROW(2:2)),2)),"",INDEX($B$2:$C$26,SMALL(IF($B$2:$B$26=$B$32,ROW($B$2:$B$26)),ROW(2:2)),2))}

The problem that I found is that the formula is not finding the first 2 rows - (Steps 1.1 & 1.2). It skips them and goes to step 1.3.

Any help to fix this problem would be appreciated.

Thanks!

 

[s hal]

my guess is your row(2:2) - this translates into typing the number 2 in your formula. If you were trying to do a nth smallest value here, you are starting at the 2nd smallest value, which is probably not the intent and is my explanation for why its skipping the first two rows

I could offer a solution to circumvent that, but I'm not entirely sure how you're using it, so maybe you will solve it yourself with that pointed out...

 

[Craig4670]

my guess is your row(2:2) - this translates into typing the number 2 in your formula. If you were trying to do a nth smallest value here, you are starting at the 2nd smallest value, which is probably not the intent and is my explanation for why its skipping the first two rows

I could offer a solution to circumvent that, but I'm not entirely sure how you're using it, so maybe you will solve it yourself with that pointed out...

Thanks for your response. I still cannot fix the problem. What do you suggest?

 

[Aladin Akyurek]

C32, control+shift+enter, not just enter:

=IFERROR(INDEX($C$2:$C$26,SMALL(IF($B$2:$B$26=$B$32,
  ROW($B$2:$B$26)-ROW($B$2)+1),ROWS($C$32:C32))),"")

 

[Craig4670]

C32, control+shift+enter, not just enter:

=IFERROR(INDEX($C$2:$C$26,SMALL(IF($B$2:$B$26=$B$32,
  ROW($B$2:$B$26)-ROW($B$2)+1),ROWS($C$32:C32))),"")

Thanks for your response. It is still not working. The cell now says FALSE.

 

[Aladin Akyurek]

Thanks for your response. It is still not working. The cell now says FALSE.

What does...

=COUNTIF(B2:B26,B32)

yield as result?

 

[Craig4670]

What does...

=COUNTIF(B2:B26,B32)

yield as result?[/QUOTE

This formula just totals how many completions there were.

 

[Aladin Akyurek]

What does...

=COUNTIF(B2:B26,B32)

yield as result?[/QUOTE

This formula just totals how many completions there were.

That means it yields a positive number... If so,

=IFERROR(INDEX($C$2:$C$26,SMALL(IF($B$2:$B$26=$B$32,ROW($B$2:$B$26)-ROW($B$2)+1),ROWS($C$32:C32))),"")

should return, copied down, the associated values from the C-range.

 

[Craig4670]

That means it yields a positive number... If so,

=IFERROR(INDEX($C$2:$C$26,SMALL(IF($B$2:$B$26=$B$32,ROW($B$2:$B$26)-ROW($B$2)+1),ROWS($C$32:C32))),"")

should return, copied down, the associated values from the C-range.

Thank you for your help. I changed the formula to =INDEX($C$2:$C$26,SMALL(IF($B$2:$B$26=$B$32,ROW($B$2:$B$26)-ROW($B$2)+1),ROWS($C$32:C32)))

It works but on cells where there is no data, the value #NUM appears. How can I modify the formula to leave it blank?

I appreciate your help!

 

[s hal]

Just add the IFERROR( your formula, "") to what you have.