Can INDIRECT return a range array?

D

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.
 
D

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.
 
B

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)
 
D

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