Need a function that returns a range.

  • Thread starter Thread starter shelfish
  • Start date Start date
S

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