Can INDIRECT return a range array?

[DaveO]

What I'm trying to do is use a INDIRECT function to return an range array so
a MATCH funtion can look into that range and find a value for me.

eg.

MATCH("Grand Total", INDIRECT("A1:A10"), 0)

This is part of a much larger formula and it's returning an N/A and I can
only assume that it's this part that is wrong as the rest seems to work in
isolation.

Any ideas that could help at all please guys.

TIA.

[DaveO]

For info the range ("A1:A10") was an example. I have another formula in the
INDRIECT in my sheet which calculates the range I need to look from.

I'm putting this in as a caveat as I'm sure I'll be told I don;t need to use
an INDIRECT if this was as simple as I portray. Just trying to make an
example of a returned range.

TIA.

"DaveO" wrote:

> What I'm trying to do is use a INDIRECT function to return an range array so
> a MATCH funtion can look into that range and find a value for me.
>
> eg.
>
> MATCH("Grand Total", INDIRECT("A1:A10"), 0)
>
> This is part of a much larger formula and it's returning an N/A and I can
> only assume that it's this part that is wrong as the rest seems to work in
> isolation.
>
> Any ideas that could help at all please guys.
>
> TIA.

[Bob Phillips]

Can you give an example that returns #N/A when it shouldn't, as that works
fine for me.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"DaveO" <[email protected]> wrote in message
news:[email protected]...
> For info the range ("A1:A10") was an example. I have another formula in
the
> INDRIECT in my sheet which calculates the range I need to look from.
>
> I'm putting this in as a caveat as I'm sure I'll be told I don;t need to
use
> an INDIRECT if this was as simple as I portray. Just trying to make an
> example of a returned range.
>
> TIA.
>
> "DaveO" wrote:
>
> > What I'm trying to do is use a INDIRECT function to return an range
array so
> > a MATCH funtion can look into that range and find a value for me.
> >
> > eg.
> >
> > MATCH("Grand Total", INDIRECT("A1:A10"), 0)
> >
> > This is part of a much larger formula and it's returning an N/A and I
can
> > only assume that it's this part that is wrong as the rest seems to work
in
> > isolation.
> >
> > Any ideas that could help at all please guys.
> >
> > TIA.

[DaveO]

Just for info for others in case they read this post, I got around the issue
by using other functions.

Formula now looks like this...

=TEXT(INDIRECT(ADDRESS(2, MATCH(0,OFFSET(INDIRECT("A" &
MATCH(J1,A$1:A$10000, 0)), 2, 1, 1, 40), 0) + 1,1)), "dd-mmm")

Obviously this is designed for my worksheet and my set-up but if you're
interested it may help you in the future.

"DaveO" wrote:

> What I'm trying to do is use a INDIRECT function to return an range array so
> a MATCH funtion can look into that range and find a value for me.
>
> eg.
>
> MATCH("Grand Total", INDIRECT("A1:A10"), 0)
>
> This is part of a much larger formula and it's returning an N/A and I can
> only assume that it's this part that is wrong as the rest seems to work in
> isolation.
>
> Any ideas that could help at all please guys.
>
> TIA.