Array stored in cell

  • Thread starter Thread starter e271828182845904523
  • Start date Start date
E

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
 
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)
 
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
 
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
 
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)
 
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
 
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)
 
Back
Top