We help IT Professionals succeed at work.

# Excel - indirect ranges intersection not working

on
625 Views
Hello,

I've been playing around with the indirect function and intersections a little but got to a point where I can't seem to make it work.

I have the following situation:
=INDEX((INDIRECT(mi_field) mi_f1),B61)  - works very well (returns the value of the first cell in the intersection

=INDEX((INDIRECT(mi_field) INDIRECT(Z47)),B61) - Dosen't work :(((( even if Z47 = mi_f1 - isn't this practically the same as the above?

Does anyone know how to get this to work if both ranges of the intersection are indirectly reffered to?

Notes:
mi_field - contains the name of another range ("January")
mi_f1 - is itself a range
(january mi_f1) - is an intersection which works

Thanks,
M
Comment
Watch Question

## View Solution Only

Commented:
If I try: =INDEX(January INDIRECT(Z47)),B61)
It also doesn't work..... but if I try =INDEX(January mi_f1),B61)  it works....

So the problem is using mi_f1 indirectly... is there any way I can solve this?

Commented:
Does something like this work:

=INDEX((INDIRECT(mi_field)&" "&INDIRECT(Z47)),B61)

Commented:
Oops parentheses in the wrong places. Try this instead

=INDEX((INDIRECT(mi_field&" "&Z47),B61)

Commented:
Hi Patrick,

I've tried and it still doesn't work, I've even tried the below, and doesn't work:
=INDEX(
INDIRECT(
INDIRECT(mi_field)&" "&INDIRECT(Z51)
)
,B61)

Commented:
Also tried the following and doesn't work :(

=INDEX(
INDIRECT(
"("&INDIRECT(Z53)&" "&INDIRECT(Z54)&")"
)
,B61)

Commented:
in my last message z53 and z54 contain the names of the ranges

Commented:
Here is a workaround that I've just found but idealy I'd like to make it work in just one line:
Steps:
1. z53=January  z54=mi_f1
2. z57=(Z53&" "&Z54)
3. =INDEX(INDIRECT(Z57),1,1)

Commented:
oops, sorry this is also not correct since it returns different values than INDEX((January mi_f1),1)

Commented:
Just one more thing that I've discovered:

If mi_f1 is a uniform range like A1:D35 then it works like this: =INDEX(INDIRECT(Z28) INDIRECT(Z29),1) where Z28=January and Z29=mi_f1

BUT, if mi_f1 is a range that is defined as: A1,B5,C2,D3,etc (so contains just some cells) then it doesn't work. even if there exists an intersection between January and mi_f1
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)

Commented:
You could do this as well:

=INDEX(INDIRECT(M2) (A16:C16,A18,A20),1,3,1)

That accesses cell C16 - it's area 1, row 1 and column 3

Commented:
mircea_a - Thanks for the grade - Patrick

Commented:
you're welcome Patrickab, now I understand how these areas and indirect function work much better.

Thanks,
Mircea
Unlock the solution to this question.

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.