# Accomodate Range in INDEX, INDIRECT

#### vivamar

##### New Member
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?

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

##### MrExcel MVP
Does this...

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

help?

#### vivamar

##### New Member
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))

##### MrExcel MVP
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

##### New Member

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

Last edited:

#### vivamar

##### New Member
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))

##### MrExcel MVP

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?

either 2 or 3

#### vivamar

##### New Member
Does D1 contains 2-3 or either 2 or 3?

D1 can contain either 2 or 3

##### MrExcel MVP
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...
Rich (BB 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?

Replies
5
Views
51
Replies
5
Views
114
Replies
3
Views
155
Replies
4
Views
220
Replies
1
Views
220

1,133,270
Messages
5,657,753
Members
418,411
Latest member
Excellency

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back