Thanks:  0
Likes:  0

# Thread: Accomodate Range in INDEX, INDIRECT

1. ## 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?

2. ## Re: Accomodate Range in INDEX, INDIRECT

Does this...

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

help?

3. ## Re: Accomodate Range in INDEX, INDIRECT

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

4. ## Re: Accomodate Range in INDEX, INDIRECT

Originally Posted by 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))
I guess tt is sorted; and it is possibly numeric.

Back to the MATCH bit...

Can D1 be 2 as well as 3?

5. ## Re: Accomodate Range in INDEX, INDIRECT

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

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

6. ## Re: Accomodate Range in INDEX, INDIRECT

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

7. ## Re: Accomodate Range in INDEX, INDIRECT

Originally Posted by 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))
Does D1 contains 2-3 or either 2 or 3?

8. ## Re: Accomodate Range in INDEX, INDIRECT

either 2 or 3

9. ## Re: Accomodate Range in INDEX, INDIRECT

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

10. ## Re: Accomodate Range in INDEX, INDIRECT

Originally Posted by vivamar
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...
Code:
```=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?