Defining an array to search by

[Guest]

I am trying to type a simple if.then formula, but would like to evaluate an
array of cells for the 2nd condition of the and statement. How do you
designate this in the formula? Here is what I currently have:

=CONCATENATE(IF(AND(F4=2,J4:R4="G"),"K40-MP1.0-N01MS",IF(AND(F4=3,J4:R4="G"),"K40-MP1.0-N01MS","K50-MP1.0-N02MS")))

If you have a better way to accomplish this task, any ideas would be
appreciated.

Thank You in advance for the help

 

[Guest]

While I do not know why you have the concatinate, I assume you do
and that you want j4=G,kr=G etc for all of the cells through R4

=if(and(concatinate(J4:R4)="GGGGGGGGG", or(F2=2,F2=3),"K40-MP1.0-N01MS",
"K50-MP1.0-N02MS" )
or
=if(And(J4:R4=1,or(F2=2,F2=3),"K40-MP1.0-N01MS", "K50-MP1.0-N02MS" )
entered as an array formula Control-shift enter

 

[RagDyeR]

First of all, your formula is an *array* formula, and therefore requires a
CSE entry.

However, it's not clear what you're looking to concatenate.

There's *no* difference if F4 is either 2 or 3 ... is that correct?

Does this *array* formula work for you:

=IF(AND(OR(F4={2,3}),J4:R4="G"),"K40-MP1.0-N01MS"&" "&"K50-MP1.0-N02MS","")

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

I am trying to type a simple if.then formula, but would like to evaluate an
array of cells for the 2nd condition of the and statement. How do you
designate this in the formula? Here is what I currently have:

=CONCATENATE(IF(AND(F4=2,J4:R4="G"),"K40-MP1.0-N01MS",IF(AND(F4=3,J4:R4="G"),"K40-MP1.0-N01MS","K50-MP1.0-N02MS")))

If you have a better way to accomplish this task, any ideas would be
appreciated.

Thank You in advance for the help

 

[Guest]

Thank you for the help. I copied this formula from some other cells that I
was using the CAT command on and forgot to remove it for this one.

You are correct, 2 or 3 does not change the outcome of the answer, so your
format improves the code.

1 more question: When do you execute the CSE? is that BEFORE you type in
the formula or AFTER.

Thanks

 

[RagDyeR]

This is how I suggest you "get" formulas from these groups:

Select the formula in the post.
Right click in the selection and choose "Copy".

Click in the cell in your WB where you wish this formula to reside, THEN,
Click in the formula bar, and immediately right click,
Choose "Paste"

NOW ... *examine* the formula in the formula bar.

The blinking "place holder" should be *immediately* at the end of the last
character in the formula.
At this point, you either hit <Enter> for a regular formula,
or <Ctrl> <Shift> <Enter> for an array formula.


*IF* the formula is long, and takes up more then a single line in the
formula bar,
you must make sure that each line in the formula bar is "full", without a
lot of empty space, signifying unwanted, invisible characters.

If you see a great deal of empty space in each line, start backwards, and
position the place holder at the end of each line and hit <Delete>.
This should compact the formula to *full* lines in the formula bar, at which
point you hit <E> or <CSE>.

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


Thank you for the help. I copied this formula from some other cells that I
was using the CAT command on and forgot to remove it for this one.

You are correct, 2 or 3 does not change the outcome of the answer, so your
format improves the code.

1 more question: When do you execute the CSE? is that BEFORE you type in
the formula or AFTER.

Thanks