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