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

[userwords]

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

Entered as an array. From this page:

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.

 

[XOR LX]

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