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

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
15,509
Office Version
  1. 365
Platform
  1. Windows
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 :)
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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.
 
Upvote 0
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.
 
Upvote 0
Solution
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top