help defining the array table by formula in vlookup funkcion

Dear friends,

I would like to ask you for help. I need you help regarding the use of name (range) in vlookup function.
I have enclosed the sheet which can help to explain my problem. I tried to make it as simply as possible. (I apologize but my English is not very good and I am not sure if I can correctly explain what I really want)
1. I have defined 3 names - " PIT, ELT, RET" , each representing the table, where first column is certain DN and second responsible value HM for certain DN. Each table must be in the different sheet. These names define the table arrays I need to use in my vlookup formula.
2. I have defined one more name - range TABLENAMES, it is table where first column is certain code and second column is name of table where to look for value for certain code.

3. IN RAW 8 I want to use the vlookup function to define the table array for other vlookup function =VLOOKUP(B8;VLOOKUP(A8;TABLENAME;2;FALSE);2;FALSE). I need to get the correct value HM for corresponding DN according the code eg. A8=EL.
Problem is that if i try to define the table array in vlookup function by other function (vlookup) does not work. Strange is that other part of vlookup function E.G. col index num can be define by any function even vlookup eg =VLOOKUP(B8;ELT;VLOOOKUP(A8;TABLENAME;2;FALSE);2;FALSE) - raw 9

Can anybody answer if it is possible to define the table array in vlookup function by other function?

I know it sound very complicated but I hope somebody can understand and help. Thanks

Hi
In cell D8, try using the INDIRECT function as in the following example:

=VLOOKUP(B8,INDIRECT(VLOOKUP(A8,TABLENAME,2,FALSE)),2,FALSE)

The INDIRECT function takes a text string and reads it as a reference.

Originally Posted by DBY

Hi
In cell D8, try using the INDIRECT function as in the following example:

=VLOOKUP(B8,INDIRECT(VLOOKUP(A8,TABLENAME,2,FALSE)),2,FALSE)

The INDIRECT function takes a text string and reads it as a reference.

Dear friend,

I would like to thank you, it works perfectly. It changes everything for me. You saved me a lot of time i had to spend crating complicated IF formulas. God bless you. Thanks.