# Embedding arrays within formulas

#### insomniac_ut

##### New Member
Hello,

I have a custom UDF that requires a 2-element array in one of the parameters of the form: {string 1, string 2}.

If I construct the formula such that the array is a 'hard' copy, i.e. =MyUDF(param1, param2, {"abc", "def"}) then it works perfectly. However, I want to be able to pull the actual string values themselves off cells. This where I've become stuck.

I've tried the following,

1. Concatenating { } with the string values such as "{"&string1&","&string2&"}". The problem with this is that the end result is "{"abc", "def"}", so Excel treats the parameter as a string rather than a 2-element array

2. Concatenating string1,string2 in a cell and then wrapping this up in an ADDRESS and INDIRECT call. So the third parameter become INDIRECT(ADDRESS(ROW(cell),COLUMN(cell))). This didn't work either.

3. I also tried "forcing" the result to become an array by wrapping the second solution in an INDEX call, so it becomes INDEX(INDIRECT(ADDRESS(ROW(cell),COLUMN(cell))),1,1). This didn't work either.

Am a bit stuck now and would appreciate any thoughts solutions. My preference would be to achieve this in-sheet without any VBA.

Thanks.

PS - I don't have the ability to change the UDF itself, it's a closed source add-in that's being used.

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Hi

Let's say that the strings "abc" and "def" are in A3 and B7. Try:

=MyUDF(param1, param2, CHOOSE({1,2},A3,B7))

Hi

Let's say that the strings "abc" and "def" are in A3 and B7. Try:

=MyUDF(param1, param2, CHOOSE({1,2},A3,B7))

Thank you, I'll try this. In the meantime if "abc" and "def" are in contiguous cells then I can simply do =MyUDF(param1, param2, range1). However, I'll try the suggested method above.

Replies
12
Views
390
Replies
10
Views
2K
Replies
20
Views
887
Replies
3
Views
1K
Replies
6
Views
739

1,202,911
Messages
6,052,508
Members
444,588
Latest member
ViJN

### 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.

### Which adblocker are you using?

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

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