Array Help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 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
 
One way ..

Place in B1, normal ENTER will do:
=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,"")

Copy B1 down to return the desired concat results as real numbers. Format
col B as Custom, Type: 000 to retain the appearance with leading zeros if
required. And if you want the concat results as text, just replace this part:
(F1&G1&H1)+0 with: F1&G1&H1 in the expression.

Note that the first line in your sample posted, viz the data in F1:H1, ie:
3,5,9 satisfies the criteria and you'd get the correct result: 359 in B1.
 
Thank you Max, Worked great! I didn't see the 359 when I built my example.
Fantastic!
Thanks again
Luke
 
Max,

Good job! This is fascinating. I was once told that MATCH( ) is one dimensional. When I first saw $C$1:$E$3 as an argument in the MATCH( ), I thought two dimensional was happening. Then, when I looked deeper, I realized that the two-dimensional array is actually the lookup_value and not the lookup_array. The lookup_array is a single cell - F1, G1 and H1 respectively. I have never seen anything like this. So, the lookup_value can be an array provided SUMPRODUCT( ) is called, right? A single cell can be the lookup_array in any MATCH( ), right? I tested it and it seemed to be the case. Please enlighten/educate me if you don't mind. We still won't label the MATCH( ) two dimensional. Correct?

A general question on IF( ):

As long as the condition returns a value greater than 0, then it is TRUE, right? It doesn't have to be 1. Please correct me if I am wrong.

I am wondering if another solution would be INDEX(MAX(ROW etc. I may be off.

Enjoy your Monday!

Epinn

You're welcome, Luke !
Thanks for the feedback
 
the lookup_value can be an array provided
SUMPRODUCT( ) is called, right?
Click to expand...

You can use any function that handles arrays: (array entered)

=OR(ISNUMBER(MATCH(A1:B2,D1:D6,0)))
A single cell can be the lookup_array in any MATCH( ), right?
Yes

We still won't label the MATCH( ) two dimensional. Correct?
Click to expand...

Correct. The lookup_array must be a 1 dimensional array.
A general question on IF( ):
As long as the condition returns a value greater than 0,
then it is TRUE, right? It doesn't have to be 1.
Click to expand...

It can be ANY NUMBER that is not 0:

A1 = -100

=IF(A1,TRUE)

An empty cell will evaluate as 0. A text entry will return #VALUE!

Biff

Max,

Good job! This is fascinating. I was once told that MATCH( ) is one
dimensional. When I first saw $C$1:$E$3 as an argument in the MATCH( ), I
thought two dimensional was happening. Then, when I looked deeper, I
realized that the two-dimensional array is actually the lookup_value and not
the lookup_array. The lookup_array is a single cell - F1, G1 and H1
respectively. I have never seen anything like this. So, the lookup_value
can be an array provided SUMPRODUCT( ) is called, right? A single cell can
be the lookup_array in any MATCH( ), right? I tested it and it seemed to be
the case. Please enlighten/educate me if you don't mind. We still won't
label the MATCH( ) two dimensional. Correct?

A general question on IF( ):

As long as the condition returns a value greater than 0, then it is TRUE,
right? It doesn't have to be 1. Please correct me if I am wrong.

I am wondering if another solution would be INDEX(MAX(ROW etc. I may be
off.

Enjoy your Monday!

Epinn

You're welcome, Luke !
Thanks for the feedback

---
 
Thanks, Biff. This is precious. I didn't know a negative number returns TRUE. Wouldn't have thought of testing with a negative number.

You won't believe how much I learned about SUMPRODUCT in the last couple of days - to be specific, data type matching. SP is not as forgiving as COUNTIF/SUMIF. I may want to coerce both sides of = or <> with &"" to be safe.

In case you want to respond to this, please go to

http://groups.google.ca/group/micro...016cd?lnk=st&q=&rnum=3&hl=en#be68f092dc3016cd

I don't want to "hijack" this thread as this is a different subject now.

Thanks again for helping me sort out one-dimensional MATCH( ) etc.

Epinn

Biff said:
the lookup_value can be an array provided
SUMPRODUCT( ) is called, right?
Click to expand...

You can use any function that handles arrays: (array entered)

=OR(ISNUMBER(MATCH(A1:B2,D1:D6,0)))
A single cell can be the lookup_array in any MATCH( ), right?
Yes

We still won't label the MATCH( ) two dimensional. Correct?
Click to expand...

Correct. The lookup_array must be a 1 dimensional array.
A general question on IF( ):
As long as the condition returns a value greater than 0,
then it is TRUE, right? It doesn't have to be 1.
Click to expand...

It can be ANY NUMBER that is not 0:

A1 = -100

=IF(A1,TRUE)

An empty cell will evaluate as 0. A text entry will return #VALUE!

Biff

Max,

Good job! This is fascinating. I was once told that MATCH( ) is one
dimensional. When I first saw $C$1:$E$3 as an argument in the MATCH( ), I
thought two dimensional was happening. Then, when I looked deeper, I
realized that the two-dimensional array is actually the lookup_value and not
the lookup_array. The lookup_array is a single cell - F1, G1 and H1
respectively. I have never seen anything like this. So, the lookup_value
can be an array provided SUMPRODUCT( ) is called, right? A single cell can
be the lookup_array in any MATCH( ), right? I tested it and it seemed to be
the case. Please enlighten/educate me if you don't mind. We still won't
label the MATCH( ) two dimensional. Correct?

A general question on IF( ):

As long as the condition returns a value greater than 0, then it is TRUE,
right? It doesn't have to be 1. Please correct me if I am wrong.

I am wondering if another solution would be INDEX(MAX(ROW etc. I may be
off.

Enjoy your Monday!

Epinn

You're welcome, Luke !
Thanks for the feedback
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
 
Hi Max,
I seen some other threads based on this one and was wondering if the first
formula you provided me earlier could 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 from yesterday; 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
 
Sorry I'm out here, Luke. For better visibility to other responders, suggest
you re-post your new query afresh. Good luck.

---
 
Back
Top