if statement issue

[bluepenink]

Hello

this is my if statement

cell S2:
=IF(J2>MAX(LEFT(R2,FIND("-",R2,1)-1),MID(R2,FIND("-",R2,1)+1,1)),"No","Yes")

R2 = 3-5 days

the issue, im having, when R2 contains data like i.e. "E2W on Wednesday" etc, it shows the N/A error. How can I make the formula state, if R2 contains anything that beings with "E", to show "Yes" as well?

pls help, thx you

 

[moonfish]

=IF(left(r2,1)="E","Yes",IF(J2>MAX(LEFT(R2,FIND("-",R2,1)-1),MID(R2,FIND("-",R2,1)+1,1)),"No","Yes"))

 

[bluepenink]

oh wow, thx you so much

one final question

is it possible to integrate another if or criteria?

a) If the R2 = "P" then "YES" also

and

b) if R2 = "" then "YES" as well

thx you so much...im all over the place wid this formula, thxs for ur help again!

 

[taurean]

Then you can add if conditions to get formula like:
IF(LEFT(R2,1)="E","Yes",IF(LEFT(R2,1)="P","Yes",IF(R2="","Yes",IF(J2>MAX(LEFT(R2,FIND("-",R2,1)-1),MID(R2,FIND("-",R2,1)+1,1)),"No","Yes"))))

 

[bluepenink]

hello guys

one final request, and i truely appreciate the responses!

-Having some issue trying to place the logic in my if statement

=IF(OR(D2="",D2="0"),"",VLOOKUP(D2,'All stores'!$C$10:$S$954,17,FALSE))


basically, i would like to further show,

if the vlookup = "" then show ""

so basically, if the vlookup answer is a blank, show a blank; it is currently showing a 0 instead.

thx you so much!

 

[taurean]

If it is a number then you do not need Double Quotes like:
=IF(OR(D2="",D2=0),"",VLOOKUP(D2,'All stores'!$C$10:$S$954,17,FALSE))

 

[bluepenink]

Oh got it, thxs and finally, i keep getting an error with the formula below


=if(ISNUMBER(vlookup(C2,'OFFICE MAX - Invoices'!$C$2:$N$3379,12,false)))

 

[sugrue]

If it is a number then you do not need Double Quotes like:
=IF(OR(D2="",D2=0),"",VLOOKUP(D2,'All stores'!$C$10:$S$954,17,FALSE))

This will still result in a 0 if the result of the vlookup is a blank. All you need to do is wrap the VLOOKUP inside a T(). For instance,
=IF(OR(D2="",D2=0),"",T(VLOOKUP(D2,'All stores'!$C$10:$S$954,17,FALSE)))

Should give you "" if the VLOOKUP is successful, but the value found is blank.

 

[bluepenink]

This will still result in a 0 if the result of the vlookup is a blank. All you need to do is wrap the VLOOKUP inside a T(). For instance,
=IF(OR(D2="",D2=0),"",T(VLOOKUP(D2,'All stores'!$C$10:$S$954,17,FALSE)))

Should give you "" if the VLOOKUP is successful, but the value found is blank.

wow that was very cool, thx you for that!