Re: #value error

Default col.A clo.B col C
ROW#
1 TENKA RAMAMUR 9/S/2/02
2 RELLA ESWARAMMA 9/D/1/02
3 RELLA APPA RAO 2/H
4 TENKA PRIYANKA 1/D/1/02
5 TENKA RINA RAJU 1/W/1
6 RELLA SEKHAR 2/S/2/02
7 TENKA SRINU 1/S/2/02
8 RELLA SRINIVAS 2/S/1/02
9 TENKA VALLABH 15/S/4/08
THIS IS SAMPLE DATA.MY FORMULA IS
c1={=small(if(left(b1:b9,find("^",substitute(b1:b9,"/","^",3)))=match(a1,a1:a9,0)&mid(b1:b9,find("^",substitute(b1:b9),"/","^",1)),find("^",substitute(b1:b9,"/","^",3))-find("^",substitute(b1:b9,"/","^",1))+1),row(b1:b9)),row(1:1))}
I would like to extract those row numbersfrom col B text values that have
three "/" strings that starts with a matching col A value row number.In my
above array formula I should get result 4 at c1 7 at c2.I am getting # value
error.I am getting these desired results at formula evaluation one step
before last step.In formula evaluation i am getting
small({false;false;#value;4;#value;false;7;false;false},1)
will you please help to correct the formula.

"Biff" wrote:

> Post the formula!
>
> Biff
>
> "TUNGANA KURMA RAJU" <TUNGANAKURMARAJU@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
> message news:E271D1F4-F3EA-471D-8189-7439B21F3EB9@xxxxxxxxxxxxxxxx
> >I am getting #value error in a formula.comparing a text string(using
> > wildcard) in a column range with a text string(wild card) I want extract
> > row
> > numbers that matches that string in a range as array formula.While
> > checking
> > in formula evaluation,to find where I did wrong,I found results are coming
> > correctly at all steps except final step.At last stage of formula
> > evaluation
> > I find =if(false,false,true,false,# value,false,true,#value) like this.The
> > true values are the results what I desire.Then I used the function
> > if(iserror,.That also didn't work.
> > Any body please help me to how can I eliminate that # value error.
>
>
>
.