important!

[pierre]

given :

A B
1 tree a 10
2 tree b 20
3 tree b 30

my search string cells are : c1 : c3
in cell D1 i entered :
=SUMPRODUCT(COUNTIF(A1:A7;"*"&IF(C1:C3<>"";C1:C3;"skip")&"*"))

in cell D2 i entered :
{=IF(ROWS(D$22)<=D$1;INDEX(A$1:A$7;SMALL(IF(ISNUMBER(SEARCH(TRANSPOSE(IF(C$1:C$3<>"";C$1:C$3));A$1:A$7));ROW(A$1:A$7));ROWS(D$22)));"")}
and i dragged down...

now if enter in cell C1 the word "tree" i get what follows :
in D2= tree a
in D3 = tree b
in D4 = tree b

NOW WHAT FORMULA SHOULD I ENTER IN E1 IN ORDER TO GET THEIR CORRESPONDING
VALUES i.e:

10 for tree a
20 for tree b
30 for tree b

thanks

 

[Bob Phillips]

IF(ROWS(D$24)<=D$1,INDEX(B$1:B$7,SMALL(IF(ISNUMBER(SEARCH(TRANSPOSE(IF($B$1:$B$3<>"",$B$1:$B$3)),B$1:B$7)),ROW(B$1:B$7)),ROWS(E$2:E4))),"")

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[pierre]

there is something wrong...i got only 30 for tree a , what about the others ??
normally i should get :

D E
2 tree a 10
3 tree b 20
4 tree c 30

HELP

 

[Bob Phillips]

It is an array formula also.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)