Array stored in cell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

e271828182845904523@gmail.com - 19 Sep 2007 07:51 GMT

I want to use the MATCH function on an array stored in a cell. MATCH(5;
{2;4;5;8};0) returns 3, as it should. However, if I set e.g. C3 to
{2;4;5;8}, MATCH(5;C3;0) gives #N/A. It seems Excel is interpreting C3
as a 1x1 array of cells, rather than reading the value of C3; i.e the
array {2;4;5;8}. The question is how to circumvent this? I have not
been able to find a function that forces interpretation of the cell
reference.

Martin

Reply to this Message

Bob Phillips - 19 Sep 2007 09:29 GMT

It works if you create a defined name as the array.

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

>I want to use the MATCH function on an array stored in a cell. MATCH(5;
> {2;4;5;8};0) returns 3, as it should. However, if I set e.g. C3 to
[quoted text clipped - 5 lines]
>
> Martin

Reply to this Message

Sebation - 19 Sep 2007 09:33 GMT

once you store the {2;4;5;8} in a cell ,it will not be an array,while your
match() function in the array.
but u can use --"Define name" --to archive this.
i.e.define an array name "AN"
={2;4;5;8}
while u can in the cell enter
=match(5,AN,0)

HTH

Regards,
Sebation.G

<e271828182845904523@gmail.com>
??????:1190184710.468896.169390@n39g2000hsh.googlegroups.com...

>I want to use the MATCH function on an array stored in a cell. MATCH(5;
> {2;4;5;8};0) returns 3, as it should. However, if I set e.g. C3 to
[quoted text clipped - 5 lines]
>
> Martin

Reply to this Message

e271828182845904523@gmail.com - 19 Sep 2007 14:25 GMT

Thanks for both your answers. However, I would like the formula using
the array to be general, and not tied up to a specific (static)
variable. Are you saying that this cannot be done?

Martin

> once you store the {2;4;5;8} in a cell ,it will not be an array,while your
> match() function in the array.
[quoted text clipped - 23 lines]
>
> - Show quoted text -

Reply to this Message

Bob Phillips - 19 Sep 2007 18:40 GMT

If you want it general, why not just put the values in separate cells and
have a real array?

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Thanks for both your answers. However, I would like the formula using
> the array to be general, and not tied up to a specific (static)
[quoted text clipped - 30 lines]
>>
>> - Show quoted text -

Reply to this Message

e271828182845904523@gmail.com - 19 Sep 2007 20:32 GMT

Because the array will have different lengths in different rows. In
one row, The array might be {1;2;5;6}, in another, {3;8}, and in a
third, {1;2;3;4;5;6;7;8;9;10;11;12}. The different lengths would
require different widths; some would require only two columns, some
12. Putting them into one column would greatly increase the
readability, and reduce the size, of the sheet.

Martin

> If you want it general, why not just put the values in separate cells and
> have a real array?
[quoted text clipped - 47 lines]
>
> - Show quoted text -

Reply to this Message

Bob Phillips - 19 Sep 2007 23:11 GMT

You could always write a UDF that returns the cell contents as an array

Function SplitCell(cell As Range, Delim As String)
SplitCell = Split(cell, Delim)
End Function

which assumes the cell contains say 1,2,3,4 and you get it with
SplitCell(A1,",")

This is not much in itself, but it can be incorporated in others, like say

=MATCH("2",SplitCell(A1,","),0)

Note that the UDF I gave returns an array of strings, so if they are numbers
that you want to check, you have to pass as strings.

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Because the array will have different lengths in different rows. In
> one row, The array might be {1;2;5;6}, in another, {3;8}, and in a
[quoted text clipped - 60 lines]
>>
>> - Show quoted text -

Reply to this Message