Excel: Accomodate Range in INDEX, INDIRECT


My formula reads like the following =INDEX(INDIRECT("'"&LOOKUP(A5,tt)&"'!A1:A20"),MATCH(D1,INDIRECT("'"&LOOKUP(A5,tt)&"'!B1:B20"),0)) where 'tt' is a table name B1 to B20 contains data that is both as single numbers as well as ranges

-
-
0
1
2-3
4
5-6
7
8
9-10
11
12-13
14
15
16-17
18
19-20
21
22
23-24
25
The formula works perfectly fine when the number in B1:B20 is a single number, but as soon as there's a range like in B5, B7 and so on, it returns a #NA How can I change the above formula to accomodate range in cells?


This question generated 31 answers. To proceed to the answers, click here.

This thread is current as of November 24, 2013.


For more resources for Microsoft Excel