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

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Does this...

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

help?
 
Upvote 0
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))
 
Upvote 0
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?
 
Upvote 0
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:
Upvote 0
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))
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

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
Back
Top