How to refer to one of several named ranges in a formula

[j_Southern]

I want to use the value of a cell to be the name of a range in a lookup formula.

In the attached workbook sheet1 has three named ranges.

Sheet 2 has a series of Index/match lookup formulae which at the moment have the value DIGI as the name of the lookup array.

What I would like to do is to have the value of AI as the name of the array. This could take any of the names of the ranges in sheet1.
Please ignore the column names in the named ranges.
Hope somebody can help here.

John

[Special-K]

Replace the DIGI in the formulas with INDIRECT(A1)

[samba_ravi]

What is the lookup value in sheet2, What is your expected result and where you want the result

[j_Southern]

SpecialK
That was where I started but it does not work. INDIRECT(A1) where A1 value is DIGI (or PLAIN or FLEXO) gives REF!

nfSales

The Sheet 2 table is giving the results I want where the lookup table is "DIGI" If I replace DIGI in the formulae on that sheet with FLEXO or PLAIN I get the results from the corresponding tables in Sheet1. as desired.
What I want to do is to refer to A1 in the formulae and change the value of A1 to reflect the table I need to use.

INDIRECT(A1) does not work. Is there something that will?
John

[j_Southern]

I have discovered that If I abandon named ranges (not desirable as they change in size in the application). Then I can use INDIRECT(A1):INDIRECT(B1) in the Index/Match formula, where A1 defines the start of the range and B2 the end.
This is very ugly though and if there is a way of retaining the names it would be much clearer and neater.
John