Vlookup for repitative data
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
|