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?

 

[markmzz]

Try this:

Layout

Descending order sheet (Sheet01)

1	926-2000		20	0-149	Sheet01
2	906-925		19	150-159
3	872-905		18	160-169
4	852-871		17	170-179
5	822-851		16	180-189
6	799-821		15	190-199
7	720-798		14	200-209
8	600-719		13	210-249
9	500-599		12	250-299
10	400-499		11	300-399
11	300-399		10	400-499
12	250-299		9	500-599
13	210-249		8	600-719
14	200-209		7	720-798
15	190-199		6	799-821
16	180-189		5	822-851
17	170-179		4	852-871
18	160-169		3	872-905
19	150-159		2	906-925
20	0-149		1	926-2000
*****	********	**	****	**********

<tbody>
</tbody>

Ascending order sheet (Sheet02)

1	0-149	Sheet02
2	150-159
3	160-169
4	170-179
5	180-189
6	190-199
7	200-209
8	210-249
9	250-299
10	300-399
11	400-499
12	500-599
13	600-719
14	720-798
15	799-821
16	822-851
17	852-871
18	872-905
19	906-925
20	926-2000
*****	********

<colgroup><col width="32" style="width: 24pt; mso-width-source: userset; mso-width-alt: 1170;"> <col width="52" style="width: 39pt; mso-width-source: userset; mso-width-alt: 1901;"> <col width="64" style="width: 48pt;"> <tbody>
</tbody>

Local names

Sheet01

DATA - Refers To: =Sheet01!$D$1:$E$20

Sheet02

DATA - Refers To: =Sheet02!$A$1:$B$20

Formulas

Sheet01 formulas - Helper table

Formula in D1 and copy to E1 and copy the range D1:E1 down until row 20

=INDEX(A$1:A$20,ROW($B$20)-ROWS(D$1:D1)+1)

Main sheet formula

=LOOKUP(D1,1*LEFT(SUBSTITUTE(INDEX(INDIRECT("'"&LOOKUP(A5,tt)&"'!DATA"),,2),"-",REPT(" ",14)),15),INDEX(INDIRECT("'"&LOOKUP(A5,tt)&"'!DATA"),,1))

Markmzz