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("^",su bstitute(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;f alse},1)
will you please help to correct the formula.

"Biff" wrote:

Post the formula!

Biff

"TUNGANA KURMA RAJU" wrote in
message ...
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.