dynamic reference to a table array vlookup excel

[joost.Kepers]

Hi All

I am trying to make a dynamic reference to a table array in a Vlookup formula.
The Table arrays points to a closed workbooks "C:\Dropbox\Agriseed\Present Trials\2018\Grass\Perennials\[Trial Data FLP1801.xlsx]FLP1801" So i can't use Indirect to the first table below.
In B5 of the second table below i have now the formula "=VLOOKUP(B$4,FLP_1801,6+$A5,0)" which brings back O2 reference (Fresh1) of the closed workbook FLP1801 of first table below and in C5 of the second table below I have "=HLOOKUP(B5,FLP_1801,2,0)" which brings back O3 reference (22 May 2018) of the closed workbook FLP1801 of the first table below
As you can see the table array is a defined Name range from the fisrt table above to the workbook spreadsheet which is close and gets update from another party.
i would like to make a formula were the table array refers to A$2 (FLP1801) of the second table below or a reference to the last table below within the same workbook but on the Input sheet G2 FLP_1801 is found by using the No1 out of the second table below B1. or an address formule maybe?
Or any other clever formula you people come up with. I do not want to use VBA as this has other problems for me.

Many thanks and hope to hear all the smart ways of solving this one.
Cheers Joost

FLP1801							Event	T/Plot Ride-on mower					T/Plot Ride-on mower
Plot Length			5				Obs		#VALUE!	#VALUE!	#VALUE!	#VALUE!	Fresh1
Field F/L	Field F/R	Sow F/L	Sow F/R	Plot	Row	Col	Reps	Variety	23 May 18	23 May 18	23 May 18			22 May 18
1	12	1	72	1	1	1	1	LP592 NEA12				#DIV/0!	#DIV/0!	1.65
24	13	2	71	24	1	2	1	LP1551				#DIV/0!	#DIV/0!	3.05
25	36	3	70	25	1	3	2	LP1555				#DIV/0!	#DIV/0!	3.05
48	37	4	69	48	1	4	2	LP1568				#DIV/0!	#DIV/0!	2.45
49	60	5	68	49	1	5	3	LP935 NEA2/6				#DIV/0!	#DIV/0!	2.75

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

	1	2	3
FLP1801	FLP1802	FLP1810
E3, Station	E3, Station	Canterbury
No.	Obs	Date	Obs	Date	Obs	Date
1	Fresh1	22-May-18	E Fresh1	07-Aug-17	E Fresh1	07-Aug-17
2	0	#NAME?	E Fresh2	04-Sep-17	#NAME?	#NAME?
3	0	#NAME?	E Fresh3	02-Oct-17	#NAME?	#NAME?
4	0	#NAME?	E Fresh4	24-Oct-17	#NAME?	#NAME?
5	0	#NAME?	E Fresh5	24-Nov-17	#NAME?	#NAME?
6	0	#NAME?	E Seedhd1	21-Dec-17	#NAME?	#NAME?
7	0	#NAME?	E Fresh6	08-Jan-18	#NAME?	#NAME?
8	0	#NAME?	E Fresh7	26-Feb-18	#NAME?	#NAME?
9	0	#NAME?	Fresh8	03-Apr-18	#NAME?	#NAME?
10	0	#NAME?	0	#N/A	#NAME?	#NAME?

<colgroup><col><col><col span="5"></colgroup><tbody>
</tbody>

F	A	N	FLP	Other	Code	No.		Trail No.	Location
1			1		FLP	1801	FLP_1801	FLP1801	E3, Station
2			2		FLP	1802	FLP_1802	FLP1802	E3, Station
3				1	FFL	1803	FFL_1803	FFL1803	E3, Station
4				2	FLH	1805	FLH_1805	FLH1805	E3, Station
5				3	FLHT	1806	FLHT_1806	FLHT1806	E3, Station
6				4	FLM	1807	FLM_1807	FLM1807	M, Station
7				5	FLMT	1808	FLMT_1808	FLMT1808	M, Station

<colgroup><col><col span="2"><col><col span="3"><col><col><col></colgroup><tbody>
</tbody>