G

#### Guest

Originally I asked:

Thank you for being there to help us,

Max SaidIs 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

=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&G1&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