Multiple ifs questions

[carabale]

Here I am again. I am having problems with a multiple if syntax.
I have three columns
Desired_Presentation (col J) | Award (col K) | SUSP_Date
Desired presentation will be blank or will contain a date.
Award has a dropdown menu with three options LOM, MSM, and FLCROSS
SUSP_Date (col L) should have a formula that will provide a message or a date depending on what is entered in columns J and K.
If J is blank and K is LOM, L will read 120 days from desired presentation
If J has a date and K is LOM, L will have a date 120 days minus date in J
If J is blank and K is MSM, L will read 60 days from desired presentation date
If J has a date and K is MSM, L will have a date 60 days minus date in J
If J is blank and K is FLCROSS, L will read 60 days from desired presentation date
If J has a date and K is FLCROSS, L will have a date 60 days minus date in J
I came up with this:
=IF (AND (K2="LOM", J2=""),"120 Days Before Presentation”, IF (AND (K2<>"LOM", J2=""),"60 Days Before Presentation",""))
But does not do all I want it to do and when I try to add ifs well it just tells me I am an idiot.
Can anyone help? Please!
Thank you as always….

 

[Special-K99]

"will have a date 120 days minus date in J"
What does this mean?
Do you mean the date in J-120 ?
Thats what Ive taken it to be.



Try this (untested)

in L2
=IF(J2="",LOOKUP(K2,{"FLCROSS","LOM","MSM"},{60,60,120})&" from presentation"),J2-LOOKUP(K2,{"FLCROSS","LOM","MSM"},{60,60,120}))

This assumes the following
J1 will either be blank or not blank
K1 will be one of FLCROSS, LOM, MSM - nothing else only one of those three values.

If this assumption is incorrect then you'll need to provide a descriptiobn of what J1 and K1 would otherwise be.

 

[carabale]

Thank you for your assistance. Your assumptions are all correct. J1 will be blank or contain a date. If there is a date, the suspense will be 120 or 60 day prior to that date. However; the formula was not accepted by Excel. Am I supposed to hit enter after the formula or other combination?

I really appreciate your effort.

 

[Special-K99]

My mistake I put too many brackets in.
Try this

=IF(J2="",LOOKUP(K2,{"FLCROSS","LOM","MSM"},{60,60,120})&" from presentation",J2-LOOKUP(K2,{"FLCROSS","LOM","MSM"},{60,60,120}))

 

[carabale]

No, unfortunately is not doing what is supposed to do. What is doing is if it finds a FL Cross in col k, it shows #N/A in col L. Also, is not doing any calculations either. Meaning, if I place a date on Col J, it shows #N/A on col L.

What do you think it is?

Please do not give up on me.

Thank you.

 

[Special-K99]

NOTE: The only expected values for K2 are FLCROSS LOM and MSM.
If you have other values in K2 then the formula may return incorrect results, though I did state this earlier.

Test for blank in J2:

1. In a blank sheet put the formula in L2. Result is #N/A since K2 is also blank.
2. Put FLCROSS in K2. Result is "60 from presentation".
3. Put LOM in K2. Result is "60 from presentation".
4. Put MSM in K2. Result is "120 from presentation".

If you are getting different results in your proper data then K2 is NOT FLCROSS / LOM / MSM

Test for date in J2:

1. In a blank sheet put the formula in L2 and a date in J2. Result is #N/A since K2 is also blank.
2. Put FLCROSS in K2. Result is J2 - 60.
3. Put LOM in K2. Result is J2 - 60.
4. Put MSM in K2. Result is J2 - 120.

So the formula does work, therefore it must be your data.

Either check your data thoroughly or upload the file to a storage site and post a link here so we can download it and examine the data to see what's actually stored there.

 

[carabale]

I am back. Sorry for not checking my mail earlier. I looked at your comments and you were right. I had a space between FL Cross. It works wonderfully. Thank you very much.

 

[carabale]

Could you help me one more time with the #N/A? How you can show a blank instead?

Thanks again.