# Help with an Array formula

#### Craig4670

##### Board Regular
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!

### Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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...

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?

C32, control+shift+enter, not just enter:
Rich (BB code):
``````=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))),"")``````

C32, control+shift+enter, not just enter:
Rich (BB code):
``````=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.

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

What does...

=COUNTIF(B2:B26,B32)

yield as result?

What does...

=COUNTIF(B2:B26,B32)

yield as result?[/QUOTE

This formula just totals how many completions there were.

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.

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?

Replies
2
Views
74
Replies
4
Views
176
Replies
8
Views
358
Replies
7
Views
337
Replies
5
Views
158

1,203,094
Messages
6,053,506
Members
444,667
Latest member
KWR21

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back