Selecting Range Names from a Table with Vlookup

[Rlb53]

Hello All....

I have a Table with a series of "Range Names" listed in each.

Depending upon the value returned in an adjacent cell I'm asking the formula to Vlookup the left column of Table1 and find the value(which designates a range). Then Match the Row (again through the Vlookup) with the associated column with a Header Value that has been selected (once again with the use of Vlookup).

The formula I've generated is as follows:

=INDEX(VLOOKUP(E23,Table1,2,FALSE),MATCH(E35,VLOOKUP(E23,Table1,3,FALSE),1) ,MATCH(100,VLOOKUP(E23,Table1,4,FALSE),0))*E28

If I plug in the range names directly into the formula it works:

=INDEX(Table1,MATCH(E35,Range1LeftColumnRange,0),MATCH(100,Range1HeaderRang e,0))*E28

The Table looks like this:

Table1
---------------Header1 -----------Header2------------------------- Header3
1 -------------Range1---- Range1LeftColumnRange -------Range1HeaderRange
2 -------------Range2---- Range2LeftColumnRange------- Range2HeaderRange
etc.

Is there some Syntax that I need to add to get the formula to recognize the values listed in Table1 as Range Names during the Vlookup?

Thanks

[mallycat]

It would be a lot easier if you prepared a sample worksheet, add your formulae and then put in some commentry in the sheet explaining what you are trying to do

[Rlb53]

Thank You !

A Sample Workbook is Attached.

[mallycat]

I am not really sure what you are trying to do, but I can help you solve your own problem.

Take a look at what I have done in your sample sheet. The trick to complex formula is to break them into their pieces in difference cells, then combine them once you have it working. So you have issues in 2 parts of your formula (cells K20, K21). Fix these, and you will fix your formula.

[Rlb53]

Thanks Mallycat,

Fixing the 2nd / 3rd part of the formula is where I'm stumped. Even in the 1st part, I'm not certain that the information returned is being recognized as a RANGE. I believe it also is being returned and recognized as Text.

=INDEX(VLOOKUP(E23,Table1,2,FALSE),MATCH(E35,VLOOKUP(E23,Table1,3,FALSE),1) ,MATCH(100,VLOOKUP(E23,Table1,4,FALSE),0))*E28


Breaking it down I'm asking it to do the following:

=INDEX( - Self Explained... No Problem

VLOOKUP(E23,Table1,2,FALSE), - I'm asking it to look in Table1 Column 2 and Retrieve the "Range Name" of a secondary Table that is defined by the value of cell E23.

MATCH(E35,VLOOKUP(E23,Table1,3,FALSE),1) , - I'm asking the formula to return the Match denoted in cell E35 from the Range identified in Column3 of Table1 which is the Left Most Column of the Table found above.

MATCH(100,VLOOKUP(E23,Table1,4,FALSE),0)) - I'm asking the same request as above for the Value of the Headers of the Table previously identified. So the INDEX may be completed. The Range Name is located in Column 4 of Table1.

*E28 - No Problem here... simple Multiplier.

When I Step through the Calculations I see the Same Errors that you noted in your returned spreadsheet. I notice that the range names are returned in " " marks that give me the indication that the Range Names are not being recognized as such, but as ordinary Text providing for the error.

That I suppose is the potential answer to my question. How may I retrieve the Range Names into the formula without them being recognized as Text?

I've tried the following Syntax to see if it would work, but the error remains.

'RangeName'!

I've substituted a written "Range Name" for the actual Cell Range without success.

$A$5:$D$10

'$A$5:$D$10'

'$A$5:$D$10'!

There are numerous Tables and Variables, hence the direction I'm attempting to go with this formula.

I suppose I could nestle a multitude of @IF statements in a cell, but I believe I'd run out of the Characters I'm allowed. (That is 255... Correct???)

Thanks for Looking at it ! It is Greatly Appreciated !!

[mallycat]

You need the indirect function.

In cell K20 in the sheet I posted, change your formula

from =MATCH(C3,L20,1)
to =MATCH(C3,INDIRECT(L20),1)

You should be able to work it out from here I think

[Rlb53]

Awesome Mate !

I'll Give it a Go.