Luke,

Thanks for re-doing. I was keen to discover if there was some circumstance I
had overlooked, it now seems that it works okay for you as given.

Regards

Bob

"Luke" wrote in message
...
Okay then, Per your specs, I re-applied the "Range Name" & "Refers to" and
then entered the second formula into B2. I pulled so hard on the elastic
that
it came up over my head, now I can see the tag.
Not sure what the heck I did but I guess that I entered in the formula
wrong
earlier. When I changed what you had given me, It did do something
similar
to the expected results but I am not so sure it would have done me any
good
down the road. It was easier to start over clean.
As always I am amazed at your intelligence with this stuff.
Thank you so much
Luke


"Bob Phillips" wrote:

Luke,

I don't know what you have done it is not what I meant at all. You seem
to
be looking through all 3 columns individually, whereas I set a range to
columns C:E, D gets included automatically. And if you notice, the first
MAX
statement subtracts 1, the second adds 1, this is so that we encompass
the 3
rows, above, including, and below the X. Similarly, by INDEX into C and
E we
encompass columns C, D and E.

My original as posted does work. Please try it again, you don't need the
change that you made.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Luke" wrote in message
...
Okaay Bob!
I got it. You forgot to include colmn B. I think perhaps you knew I
would
get it:).
Here it is
In B2 Define Range name "lastX" Refer to as follows:


=INDEX($C$1:$C$20,MAX(IF($F$1:$F1="X",ROW($F$1:$F1 )))-1):INDEX($D$1:$D$20,MA

X(IF($F$1:$F1="X",ROW($F$1:$F1)))-1):INDEX($E$1:$E$20,MAX(IF($F$1:$F1="X",RO
W($F$1:$F1)))+1)

Then in B2 paste:


=IF(COUNTIF($F1:$F3,"X")0,"",IF(AND(SUMPRODUCT(--ISNUMBER(MATCH(lastX,C2,0)

)),SUMPRODUCT(--ISNUMBER(MATCH(lastX,D2,0))),SUMPRODUCT(--ISNUMBER(MATCH(l
as
tX,E2,0)))),(C2&D2&E2)+0,""))

and it works great!
Thanks A bunch!
Luke


"Bob Phillips" wrote:

Okay,

It is a two-step approach.

First select cell B2, and add a defined name (InsertNamedefine...)
with a
name of lastX and a RefersTo value of



=INDEX($C$1:$C$20,MAX(IF($F$1:$F1="X",ROW($F$1:$F1 )))-1):INDEX($E$1:$E$20,MA
X(IF($F$1:$F1="X",ROW($F$1:$F1)))+1)

Then in cell B2, note, leave B1 clear, add the formula

=IF(COUNTIF($F1:$F3,"X")0,"",IF(AND(
SUMPRODUCT(--ISNUMBER(MATCH(lastX,C2,0))),
SUMPRODUCT(--ISNUMBER(MATCH(lastX,D2,0))),
SUMPRODUCT(--ISNUMBER(MATCH(lastX,E2,0)))),(C2&D2&E2)+0,""))

and copy down

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Luke" wrote in message
...
Max asked me to re-post this in order to get more visibility.
Originally I asked:
Thank you for being there to help us,
Is it possible to have a formula in colmn B that will look at
any
three
individual cells in the array C1:E3 and match them up to any one
row
from the
array F1:H11, then display a cancatenation of three like cells
from
array
F1:H11 in Colmn B?

I hope this example comes through well enough for you.
Thank you,
Luke
A B C D E F G H
1 359 1 6 3 3 5 9
2 0 0 1 7 4 4
3 100 9 8 5 1 0 0
4 2 1 3
5 6 4 3
6 168 1 6 8
7 1 6 4
8 3 3 4
9 4 4 4
10 950 9 5 0
11 8 8 7

Max Said:



=IF(AND(SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,F1,0))),SUMPRODUCT(--ISNUMBER(


MATCH($C$1:$E$3,G1,0))),SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,H1,0)))),(F1&G
1&H1)+0,"")

<<<<<<<<<<<<<<

Worked great for what it was intended to do. Now I am asking if
that
formula
can be tweaked a bit more.
for example the formula currently looks at $C$1:$E$3 and matches
the
corresponding row results in F1:H11 while staying locked into
$C$1:$E$3.
Now that I see it can be done, I changed my data so that it is all
in
the
same colmns (see example below).
Until now I would manually pull out the range $C$1:$E$3 and then I
looked
for the results.. you helped me with that.
In colmn "F" I marked the middle row (C2:E2) of the range
$C$1:$E$3
with
an
"X" and note the "X" at (C10:E10) of $C$9:$E$11 as well as
(C17:E17)
of
$C$16:$E$18 .
I would like to know if it is possible to have the same formula
keep
it's
original search until it finds another "X" in colmn "F" then
unlock
from
$C$1:$E$3 (ie $C1:$E3) then lock onto that new range, in this case
it's
$C$9:$E$11, until it finds another "X" and so on.
To be clear, the range that "X" represents would always have three
total
rows as in the original thread; including the row that
contains the "X", one row above and one row below. As well it
would
not
look
back at previous ranges but once locked into arange it would keep
matching
on
the current "X" Range until it finds another... I think it's a
streatch
but I
am always amazed at what you guys can do.
Thanks again for your help,
Luke
A B C D E F
1 1 6 3
2 0 0 1 X
3 9 8 5
4 359 3 5 9
5 7 4 4
6 100 1 0 0
7 2 1 3
8 6 4 3
9 168 1 6 8
10 1 6 4
11 3 3 4
12 4 4 4 X
13 9 5 0
14 8 8 7
15 435 4 3 5
16 5 6 1
17 3 0 5 X
18 1 6 8
19 315 3 1 5
20 4 4 5

Thank you for your help. It is alright if it is impossible as I
can
work
with what I have already.
Luke