#value error

T

TUNGANA KURMA RAJU

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.
 
T

TUNGANA KURMA RAJU

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 I
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.
 
R

Roger Govier

Hi

The failures are arising because there are some cells in column B that do
not have 3 "/"'s.

You are making life very difficult for yourself trying to achieve the result
in a single cell formula.
Why not have a helper column where your S/2 has been extracted, (using the
Mid,Find,Substitute formula you have)and just carry out your tests on this
new column of extracted data.
You could deal with the lack of 3 /'s in cells during this extraction by
=IF(LEN(B1)-LEN(SUBSTITUTE(B1,"/",""))<3 , "" , your_extraction_formula....)
Then you need only look for identical values in this column, where the cells
are not equal to null.

You can always hide the column if you don't want it to show, or make it a
column far off to the right of the normally viewed area of data.



Regards

Roger Govier
 
Top