match true exact "dd" vs. find next 5th character=y
 

hi, not sure if know how/ what to ask, am trying to find the next cell in a
column, where the 5th letter = y

MATCH(TRUE,EXACT($D$373:$D$826,"dd"),0) seems to work in a formula using,
MATCH(TRUE,AND(LEN(R9)=5,MID(R9,5,1)="y"),0) but this does not

do I need to somehow change value of R9 to reflect the array.. using column R

following might be too much, problem is above;
1st example works, the 2nd does not;
exact paste to a cell will show soft returns for easy edit; BU13 is column
cursor ends up in / row that this formula has to be in; thanks; item is a
hyperlink to the 1st max value in that column).

=HYPERLINK(IF(ISNA(INDEX(ROW(BU$571:BU$587)-ROW(BU$571)+1,INDEX(ROW(BU$571:BU$587),MATCH(MAX(B U$571:BU$587),BU$571:BU$587,0)))),"",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBS TITUTE(CELL("address",BU13),"",""),ROW(),"")&INDEX (ROW(BU$571:BU$587),MATCH(MAX(BU$571:BU$587),BU$57 1:BU$587,0))),$W$2,0))),
MAX(IF(ISNUMBER(BU$571:BU$587),BU$571:BU$587,-1E+100)))

not working: (R17: col cursor ends up in, row 17 where this formula.. would
need to be to work) thanks;

=HYPERLINK(
IF(ISNA(INDEX(ROW($R$373:$R$1402)-ROW($R$373)+1,INDEX(ROW($R$373:$R$1402),MATCH(TRUE ,AND(LEN(R9)=5,MID(R9,5,1)="y"),0)))),"",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBS TITUTE(CELL("address",$R17),"$",""),ROW(),"")&INDE X(ROW($R$373:$R$1402),MATCH(TRUE,AND(LEN(R9)=5,MI D(R9,5,1)="y"),0))),$W$2,0))),
IF(ISNA(INDEX(ROW($R$373:$R$1402)-ROW($R$373)+1,MATCH(TRUE,AND(LEN(R9)=5,MID(R9,5,1 )="y"),0))),"",INDEX(ROW($R$373:$R$1402),MATCH(TRU E,AND(LEN(R9)=5,MID(R9,5,1)="y"),0))))

match true exact "dd" vs. find next 5th character=y
 

MATCH(TRUE,EXACT($D$373:$D$826,"dd"),0) seems to work in a formula using,
MATCH(TRUE,AND(LEN(R9)=5,MID(R9,5,1)="y"),0) but this does not

trying the following idea, not quite working yet:

MATCH(TRUE,EXACT(AND(LEN($AY$373:$AY$1402)=5,MID( $AY$373:$AY$1402,5,1),"Y")),0)

match true exact "dd" vs. find next 5th character=y
 

he problem is mid(R9,5,1)="y" should be replace with
if(find(mid(r9,5,1),"y") < 0)

"nastech" wrote:



MATCH(TRUE,EXACT($D$373:$D$826,"dd"),0) seems to work in a formula using,
MATCH(TRUE,AND(LEN(R9)=5,MID(R9,5,1)="y"),0) but this does not

trying the following idea, not quite working yet:

MATCH(TRUE,EXACT(AND(LEN($AY$373:$AY$1402)=5,MID( $AY$373:$AY$1402,5,1),"Y")),0)

match true exact "dd" vs. find next 5th character=y
 

hi, thanks for responding. will changing R9 to a range work with this?

"Joel" wrote:

he problem is mid(R9,5,1)="y" should be replace with
if(find(mid(r9,5,1),"y") < 0)

"nastech" wrote:



MATCH(TRUE,EXACT($D$373:$D$826,"dd"),0) seems to work in a formula using,
MATCH(TRUE,AND(LEN(R9)=5,MID(R9,5,1)="y"),0) but this does not

trying the following idea, not quite working yet:

MATCH(TRUE,EXACT(AND(LEN($AY$373:$AY$1402)=5,MID( $AY$373:$AY$1402,5,1),"Y")),0)

match true exact "dd" vs. find next 5th character=y
 

there is no need for the match function. You are just looking for tru or
false that will be return with the AND function

=if(A5=1,,) will return either true of false.

which is equivalent to - this is what you want

=if(A5=1,and(true,true),and(true,true))

You don't do - this is what your code is generating

=if(a5=1,match(true,true),match(false,false))



"nastech" wrote:

hi, thanks for responding. will changing R9 to a range work with this?

"Joel" wrote:

he problem is mid(R9,5,1)="y" should be replace with
if(find(mid(r9,5,1),"y") < 0)

"nastech" wrote:



MATCH(TRUE,EXACT($D$373:$D$826,"dd"),0) seems to work in a formula using,
MATCH(TRUE,AND(LEN(R9)=5,MID(R9,5,1)="y"),0) but this does not

trying the following idea, not quite working yet:

MATCH(TRUE,EXACT(AND(LEN($AY$373:$AY$1402)=5,MID( $AY$373:$AY$1402,5,1),"Y")),0)