Use a cell value to form part of an array constant, how would you do it?

[jasonb75]

A small weekend challenge for you all,

I've had a few occasions where it would be useful to combine ranges with array constants, as with the psuedo formula below. Got a few ideas for workarounds, which I won't post now to avoid any bias on your ideas, just wondered how others might do this.

=lookup(A1,{x,y,z,B1})

The values can be anything you like, text or numeric, x,y,z represent and number of hadwritten constants, B1 can be a single cell value or range but must form part of the array constant.

I'm going to be AFK for most of the day, will have a look and post my own ideas when I get back.

Thanks and good luck in finding something

 

[Aladin Akyurek]

A small weekend challenge for you all,

I've had a few occasions where it would be useful to combine ranges with array constants, as with the psuedo formula below. Got a few ideas for workarounds, which I won't post now to avoid any bias on your ideas, just wondered how others might do this.

=lookup(A1,{x,y,z,B1})

The values can be anything you like, text or numeric, x,y,z represent and number of hadwritten constants, B1 can be a single cell value or range but must form part of the array constant.

I'm going to be AFK for most of the day, will have a look and post my own ideas when I get back.

Thanks and good luck in finding something

A method I introuduced eons ago is:

<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=192><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 width=64>FAD</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>FAD</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64 align=right>2</TD></TR></TBODY></TABLE>

=MATCH(A1,CHOOSE({1,2},{"a","b",3},B1),0)

I avoided LOOKUP for it requires a sorted vector/array, but the principle is the same.

 

[Aladin Akyurek]

Expanding a bit...

<TABLE style="WIDTH: 240pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=320><COLGROUP><COL style="WIDTH: 48pt" span=5 width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=64>FAD</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>FAD</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=right>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=center>#N/A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>a</TD></TR></TBODY></TABLE>

C1:

=MATCH(A1,CHOOSE({1,2},{"a","b",3},B1),0)

CHOOSE yields here:

{"a","FAD",#N/A}


D1:

=MATCH(A1,CHOOSE({1,2},{"a";"b";3},B1),0)

CHOOSE yields here:

{"a","FAD";"b","FAD";3,"FAD"}


E1:

=VLOOKUP(A1,CHOOSE({1,2},B1,{"a";"b";3}),2,0)

CHOOSE yields here:

{"FAD","a";"FAD","b";"FAD",3}

With some judicious use, we can simulate...

ARRAY.JOIN of the morefunc.xll add-in and

ARRAYUNION

See e.g., http://www.mrexcel.com/forum/showthread.php?t=104897 for the latter.

 

[jasonb75]

Thanks Aladin,

I don't think I would ever have thought of using CHOOSE to do this, I was trying other things that failed but my best attempts were

Including the header in the range array and substituting it for a constant

=LOOKUP(B2,--SUBSTITUTE($A$1:$A$7,A1,0))

or for the reverse method, substituting abnormal chararcters in the array constant with range values.

=LOOKUP(B3,--SUBSTITUTE(SUBSTITUTE({0,1,"|","||"},"|",A3,1),A3&"|",C3))

Both of which have obvious limitations on use, and potential causes to fail.

I have morefunc installed anyway so maybe ARRAY.JOIN would be the preferable choice, something I will have to look into.

Thanks for the information.

 

[Weaver]

This might fall into the category of "if you don't understand it, you'll never need it" but just in case, would anyone care to explain?

Aladin's 'expansion' was particularly baffing and I know it's going to bother me if I don't fully understand what it is you're trying to do here and why.

Or am I just having a Monday Morning Moment?

 

[jasonb75]

With regards to what and why, consider passing an array to a formula, (lookup was used purely for example), you can use either a range, or an array constant, but not both in the same argument.

While nothing specific at this time, I have had numerous occasions where I've wanted to use a range, or even function in an array constant {1,2,3,A1:B1} for simple example, I'm trying to find various possible ways to achieve this, some things work, some don't.

I'm having the same with Aladin's suggestions, I think I see the basic principle but applying it to data doesn't seem to be working so well.

 

[Aladin Akyurek]

With regards to what and why, consider passing an array to a formula, (lookup was used purely for example), you can use either a range, or an array constant, but not both in the same argument.

While nothing specific at this time, I have had numerous occasions where I've wanted to use a range, or even function in an array constant {1,2,3,A1:B1} for simple example, I'm trying to find various possible ways to achieve this, some things work, some don't.

I'm having the same with Aladin's suggestions, I think I see the basic principle but applying it to data doesn't seem to be working so well.

The best use is to put non-conitguous single cells together into an array as in:

With...
A2: a
F4: x
K1: 3

CHOOSE({1,2,3},A2,F4,K1)

gives a horizontal array:

{"a","x",3}

The following two non-contiguous ranges give you a 2x3 table:

=CHOOSE({1,2},A6:A9,D6:D9)

===>

{1,"q";3,"p";2,"o";5,"r"}

Dimensions of the objects you bring together determine the result object.