Need a function that returns a range.

[shelfish]

Hi all,

I'm in search of a WORKSHEET function that returns a range. I realize
that this can easily be done in VB but that's not an option at this
time.

So, for all the functions that have as an argument "lookup_array,"
what can I use to return a range. For instance:

=MATCH("myValue",ROWS(1),FALSE)

Obviously, this throws an error bc rows(1) isn't an array. So what
could I put in it's place.

Many thanks for any suggestions,
S.

 

[Rick Rothstein \(MVP - VB\)]

If you know A1 (for this example) always contains one of the listed text
values inside the curly braces, you could use this directly...

=MATCH(A1,{"one","two","three","four"},0)

However, if A1 contains a value that does not match one of the those in the
list, it will generate an error, so you would need to do something like
this...

=IF(ISNUMBER(MATCH(A1,{"one","two","three","four"},0)),MATCH(A1,{"one","two","three","four"},0),"???")

where you would replace the "???" with whatever you wanted to display
whenever A1 did not contain a value in the list.

Rick

 

[Rick Rothstein \(MVP - VB\)]

Also, if you had your list of items in say, D1 through D4, you could do
this...

=MATCH(A3,D14,0)

but the Help file examples show this usage which is why I didn't think to
give you this answer first.

Rick