Need a function that returns a range.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

shelfish - 30 May 2008 06:04 GMT

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.

Reply to this Message

Rick Rothstein (MVP - VB) - 30 May 2008 06:29 GMT

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

> Hi all,
>
[quoted text clipped - 12 lines]
> Many thanks for any suggestions,
> S.

Reply to this Message

Rick Rothstein (MVP - VB) - 30 May 2008 06:47 GMT

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

=MATCH(A3,D1:D4,0)

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

Rick

> If you know A1 (for this example) always contains one of the listed text
> values inside the curly braces, you could use this directly...
[quoted text clipped - 28 lines]
>> Many thanks for any suggestions,
>> S.

Reply to this Message