Accomodate Range in INDEX, INDIRECT

vivamar

New Member
Joined
Nov 2, 2013
Messages
29
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

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
Joined
Nov 2, 2013
Messages
29
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
Joined
Nov 2, 2013
Messages
29

ADVERTISEMENT

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
Joined
Nov 2, 2013
Messages
29
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

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?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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?
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top