# I dont understand how this part work SMALL(IF(\$A\$1:\$A\$7=\$A\$10;ROW(\$A\$1:\$A\$7));ROW(1:1))

#### userwords

##### New Member
In =INDEX(\$A\$1:\$B\$7;SMALL(IF(\$A\$1:\$A\$7=\$A\$10;ROW(\$A\$1:\$A\$7));ROW(1:1));2)

Creating a List of All Non-Blank Cells in a Column | Excel Array Formulas

A B C
 1 2 3 fruit papaya potato vegetable cauliflower plates potato table crockery tomato #NUM! plates #NUM! furniture sofa #NUM! table #NUM! #NUM! 0 #NUM!

<colgroup><col span="2"><col></colgroup><tbody>
</tbody>

I understand it gets the row number of each empty cell to put with in the index with the column number of the second column 2. But i dont understand the steps the array takes to make it.

Any explanation on your part may help me.

I dont like use functions i dont understand at all.

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### XOR LX

##### Well-known Member
Hi.

Actually that's not a very rigorous construction at all.

Firstly, as it stands it is not flexible enough to work should your data not actually begin in row 1. Much better than:

ROW(\$A\$1:\$A\$7)

is either:

ROW(\$A\$1:\$A\$7)-MIN(ROW(\$A\$1:\$A\$7))+1

or:

ROW(\$A\$1:\$A\$7)-ROW(\$A\$1)+1.

Secondly, the use of ROW(1:1) for the k parameter is not the best choice, since row insertions within the sheet would cause this set-up to give incorrect results. Much better is ROWS(\$1:1).

Regards

Replies
5
Views
302
Replies
2
Views
887
Replies
1
Views
997
Replies
0
Views
282
Replies
7
Views
1K

1,195,682
Messages
6,011,134
Members
441,586
Latest member
rodsin76

### 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