Array Formula with Concatenate and If

[Kris_Wright_77]

I think that using an array formula will solve my little problem, but I only
understand a little about them, and the formulae are only returning blanks.

I have a table of data, that I wish to rearrange into a grid based on
entries into 2 columns with restricted values allowed.

The data is in the format, and the headers define the Named Ranges
ID# Description Y-axis X-axis
-------------------------------------------
01 Apples A Left
02 Oranges B Left
03 Bread C Center
04 Table B Right
05 Desk C Center
......

The description column is not to be returned, but I have included it in case
it requires a different solution.

I have then entered formula similar to the following in a grid
{=Concatenate(If(and(Y-Axis=C,X-Axis=Center),Text(ID#," 00,"),""))}
and was expecting it to return the result, " 03, 05,"

so the complete grid would like

| Left | Center | Right |
--|---------|-------------|---------|
A | 01, | | |
--|---------|-------------|---------|
B | 02, | | 04, |
--|---------|-------------|---------|
C | | 03, 05, | |
--|---------|-------------|---------|

Could someone let me know why this isnt working, or some other way in which
it can be achieved

Thanks very much in advance

Kris

 

[Dave F]

Well, there are a couple of things that occur to me:

1) Are Y-Axis and X-Axis defined names, or are they values? If they are the
latter, then your IF(AND( statement won't work.

I would do something like =IF(AND(C1="A",D1="Left"),CONCATENATE(A1,B1),"")
and fill down as necessary. This formula assumes that the ID column is in
A:A, etc. Adjust the formula to suit your needs.

But I would put the IF(AND( statement on the outside, and, depending on
whether the condition returns TRUE, then CONCATENATE, else return an
alternative (in my example, an empty string.)

Dave

 

[Kris_Wright_77]

Dave

Thanks for your quick response.

I have tried the formula you proposed, but I cannot get it to produce the
results in the grid as I wanted. - Your formula concatenates the ID# &
Description if 2 conditions are true.
What I need is to concatenate several ID# where the 2 conditions are true.

And the X-Axis, Y-Axis and ID# are defined named ranges (single column array).

I did see another similar post with If and Arrays that gave me the idea of
trying to do it this way (but I cant find it now).

If you need any more detail on what I am aiming to do, please let me know.

Kris