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