Array stored in cell

[e271828182845904523]

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

 

[Bob Phillips]

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

--
---
HTH

Bob

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

 

[Sebation]

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]

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

 

[Bob Phillips]

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



--
---
HTH

Bob

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

 

[e271828182845904523]

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

 

[Bob Phillips]

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.

--
---
HTH

Bob

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