On Oct 10, 11:23*am, isabelle wrote:
hi,

I'll try to explain the way I understand

Product ID
Abc 101
Abc 100
Abb 105
Abb 107
Abc 102
Abb 109

* =INDEX(ID,SMALL(IF(Product="Abc",ROW(INDIRECT("1:" &ROWS(Product)))),ROW()))

Product="Abc" evaluates each cell in the range and returns TRUE or FALSE as an internal array
True
True
False
False
True
False

* ROW(INDIRECT("1:"&ROWS(Product))) returns an array containing the line number

* IF(Product="Abc",ROW(INDIRECT("1:"&ROWS(Product)))
then we check each value of internal array "true or false" to return the line number
note that we start at line 1 for the INDEX function

now the internal array is:

2
3
0
0
6
0

SMALL(internal array,ROW())
Return a new internal array :

2
3
6
0
0
0

=INDEX(ID,internal array)
Return:

101
100
102
#NUM!
#NUM!
#NUM!

--
isabelle

Its really cool !!!

it more clear now to me, I think I can use any type of array formula
henceforth
thanks a lot!!!
isabelle