Microsoft Excel - How to use name reference from an array formula in vlookup

I've got data in 4 different tables.
I can use vlookup and table name to find what I need:
=VLOOKUP(B3,FFTDSubjCum,18,FALSE) 
but I want user to be able to select a value in validation list and then a formula to match that value to relevant table.

the array formula: 
=INDEX(FFTSbj!$F$1:$F$4,MATCH(1,(C3=FFTSbj!$B$1:$B$4)*(D3=FFTSbj!$E$1:$E$4),0))
returns defined name of a table: FFTDSubjCum 

How can I use it to achieve something what vlookup would do: if I put that array formula as part of vlookup it wont work:
=VLOOKUP(B3,INDEX(FFTSbj!$F$1:$F$4,MATCH(1,(C3=FFTSbj!$B$1:$B$4)*(D3=FFTSbj!$E$1:$E$4),0)),18,FALSE)  

Thank you

You might be able to use Indirect:

=VLOOKUP(B3,Indirect(INDEX(FFTSbj!$F$1:$F$4,MATCH(1,(C3=FFTSbj!$B$1:$B$4)*(D3=FFTSbj!$E$1:$E$4),0))),18,FALSE)

I'm not sure it will work embedded in the VLookup, you might need to use a helper cell/column to get the table name, then point to that within the VLookup:

=VLOOKUP(B3,Indirect(C3),18,FALSE)

Where you have your array index formula in C3.

Hello Ewa,

If you have a cell (say F2) with the relevant table name in it then the following formula will work (which is exactly what Wally said).  F2 could be populated from a validation list or by your formula though I am not sure what your formula does exactly without more detail.

=VLOOKUP(C2,INDIRECT(F2),2,FALSE)

Harry

Hello Again Ewa,

I have had a look at your array formula to determine which table to look in and it appears that the values in C3 and D3 have to be linked to give a valid value therefore a simple VLOOKUP based on one of them would give you the result.

However if you use wally's INDIRECT with the array formula that will work as well.

{=VLOOKUP(B3,INDIRECT(INDEX(FFTSbj!$F$1:$F$4,MATCH(1,(C3=FFTSbj!$B$1:$B$4)*(D3=FFTSbj!$E$1:$E$4),0))),18,FALSE) }

Harry

Thank you both, INDIRECT works great! 

Thank you both, INDIRECT works great! 

Thank you both, INDIRECT works great!