If F2 = "m" or "m3" or "a" or "a3" or "ftm," and J2 is not blank, I want to
return a value of 1.

Can anyone kindly help?

Thanks,
Bob

One way.

=IF(AND(LOOKUP(F2,{"a","a3","ftm,","m","m3"},{1,1, 1,1,1})=1,ISBLANK(J2)=FALSE),1,"")
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"bob" wrote:

If F2 = "m" or "m3" or "a" or "a3" or "ftm," and J2 is not blank, I want to
return a value of 1.

Can anyone kindly help?

Thanks,
Bob

Barb,

Excellent! Thank you very much.

Bob

"Barb Reinhardt" wrote:

One way.

=IF(AND(LOOKUP(F2,{"a","a3","ftm,","m","m3"},{1,1, 1,1,1})=1,ISBLANK(J2)=FALSE),1,"")
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"bob" wrote:

If F2 = "m" or "m3" or "a" or "a3" or "ftm," and J2 is not blank, I want to
return a value of 1.

Can anyone kindly help?

Thanks,
Bob

On Nov 2, 4:07*pm, bob wrote:
If F2 = "m" or "m3" or "a" or "a3" or "ftm,"
and J2 is not blank, I want to return a value of 1.

First, when you say "J2 is blank" (the opposite of "not blank"), do
you mean that it __appears__ blank, or do you mean that it really has
no formula? In other words, is the formula =if(true,"") blank or not
blank in your mind?

This is important because ISBLANK() is true only when J2 has no
formula or value, whereas J2="" is true whenever J2 appears blank.
(Well, technically, that should be TRIM(J2)="". But that is usually
overkill.)

Assuming you want the latter, one way to do what you ask without any
function overhead is:

=(J2<"") * ((F2="m")+(F2="m3")+(F2="a")+(F2="a3")+(F2="ftm"))

But your subject line suggests that you are interested in an IF/AND/OR
solution per se, perhaps because you are trying to understand that
combination. In that case:

=if(and(J2<"", or(F2="m",F2="m3",F2="a",F2="a3",F2="ftm")),1,0)

Or more simply:

=if(and(J2<"", or(F2={"m","m3","a","a3","ftm"})),1,0)

Or more simply:

=--and(J2<"", or(F2={"m","m3","a","a3","ftm"}))

Note: Replace J2<"" with ISBLANK(J2)=FALSE if you truly want to know
if the cell is empty (i.e. no value or formula).

Hi,

Suppose you enter your list in the range A1:A5, then the formula is

=SUMPRODUCT((F2=$A$1:$A$5)*(J2<""))

This allows for easy modifications.

--
Thanks,
Shane Devenshire


"bob" wrote:

If F2 = "m" or "m3" or "a" or "a3" or "ftm," and J2 is not blank, I want to
return a value of 1.

Can anyone kindly help?

Thanks,
Bob