Accomodate Range in INDEX, INDIRECT

[vivamar]

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

<tbody>
</tbody>

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?

 

[Aladin Akyurek]

Does this...

=INDEX(INDIRECT("'"&VLOOKUP(A5,tt,2,0)&"'!A1:A20"),MATCH(D1,INDIRECT("'"&VLOOKUP(A5,tt,2,0)&"'!B1:B20"),0))

help?

 

[vivamar]

Thanks for the answer Aladin. The 'tt' holds the name of excel sheets, so I do not want any change there.

I think I need to change something in the second portion of the formula to accomodate cells containing range MATCH(D1,INDIRECT("'"&LOOKUP(A5,tt)&"'!B1:B20"),0))

 

[Aladin Akyurek]

Thanks for the answer Aladin. The 'tt' holds the name of excel sheets, so I do not want any change there.

I think I need to change something in the second portion of the formula to accomodate cells containing range MATCH(D1,INDIRECT("'"&LOOKUP(A5,tt)&"'!B1:B20"),0))

I guess tt is sorted; and it is possibly numeric.

Back to the MATCH bit...

Can D1 be 2 as well as 3?

 

[vivamar]

A1 to A20 contains a sequential number from 1 to 20

B1 to B20 contains this

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

<tbody>
</tbody>

tt contains this

5.0 5.2 to 5.7
5.8 5.8 to 7.1

As you can see, I am not really bothered by the Named table as it just looks up the sheet names.

D1 can contain only a single number at a time from 0 to 25. I have to take this number and lookup B1 to B20 and pick corresponding A1 to A20 value. Since the column B contains ranges like 23-24 or 9-10, the formula is failing

 

[vivamar]

Anybody? Just need to know what do I put here instead of the Bold text to accomodate ranges

MATCH(D1,INDIRECT("'"&LOOKUP(A5,tt)&"'!B1:B20"),0))

 

[Aladin Akyurek]

Anybody? Just need to know what do I put here instead of the Bold text to accomodate ranges

MATCH(D1,INDIRECT("'"&LOOKUP(A5,tt)&"'!B1:B20"),0))

Does D1 contains 2-3 or either 2 or 3?

 

[vivamar]

either 2 or 3

 

[vivamar]

Does D1 contains 2-3 or either 2 or 3?

D1 can contain either 2 or 3

 

[Aladin Akyurek]

either 2 or 3

The table you put up runs from B1 to B21, not to B20. Adjust A1:A20 and B1:B20 to suit if necessary.

Note that I tested with a single range...

=INDEX(
  INDIRECT("'"&LOOKUP(A5,tt)&"'!A1:A20"),
   LOOKUP(9.99999999999999E+307,
    SEARCH("-"D1&"-","-"&INDIRECT("'"&LOOKUP(A5,tt)&"'!B1:B20")&"-"),
    ROW(B1:B20)-ROW(B1)+1))

Does this deliver the good?