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

[TABLE="width: 1058"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 2"]FLP1801[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Event [/TD]
[TD]T/Plot Ride-on mower[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]T/Plot Ride-on mower[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Plot Length[/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Obs[/TD]
[TD][/TD]
[TD]#VALUE![/TD]
[TD]#VALUE![/TD]
[TD]#VALUE![/TD]
[TD]#VALUE![/TD]
[TD]Fresh1[/TD]
[/TR]
[TR]
[TD]Field F/L[/TD]
[TD]Field F/R[/TD]
[TD]Sow F/L[/TD]
[TD]Sow F/R[/TD]
[TD]Plot[/TD]
[TD]Row[/TD]
[TD]Col[/TD]
[TD]Reps[/TD]
[TD]Variety[/TD]
[TD]23 May 18[/TD]
[TD]23 May 18[/TD]
[TD]23 May 18[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]22 May 18[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]12[/TD]
[TD]1[/TD]
[TD]72[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]LP592 NEA12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#DIV/0![/TD]
[TD]#DIV/0![/TD]
[TD]1.65[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]13[/TD]
[TD]2[/TD]
[TD]71[/TD]
[TD]24[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]LP1551[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#DIV/0![/TD]
[TD]#DIV/0![/TD]
[TD]3.05[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]36[/TD]
[TD]3[/TD]
[TD]70[/TD]
[TD]25[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]LP1555[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#DIV/0![/TD]
[TD]#DIV/0![/TD]
[TD]3.05[/TD]
[/TR]
[TR]
[TD]48[/TD]
[TD]37[/TD]
[TD]4[/TD]
[TD]69[/TD]
[TD]48[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]LP1568[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#DIV/0![/TD]
[TD]#DIV/0![/TD]
[TD]2.45[/TD]
[/TR]
[TR]
[TD]49[/TD]
[TD]60[/TD]
[TD]5[/TD]
[TD]68[/TD]
[TD]49[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]3[/TD]
[TD]LP935 NEA2/6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#DIV/0![/TD]
[TD]#DIV/0![/TD]
[TD]2.75[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 560"]
<colgroup><col><col><col span="5"></colgroup><tbody>[TR]
[TD] [/TD]
[TD="colspan: 2"]1[/TD]
[TD="colspan: 2"]2[/TD]
[TD="colspan: 2"]3[/TD]
[/TR]
[TR]
[TD="colspan: 2"]FLP1801[/TD]
[TD="colspan: 2"]FLP1802[/TD]
[TD="colspan: 2"]FLP1810[/TD]
[/TR]
[TR]
[TD="colspan: 2"]E3, Station[/TD]
[TD="colspan: 2"]E3, Station[/TD]
[TD="colspan: 2"]Canterbury[/TD]
[/TR]
[TR]
[TD]No.[/TD]
[TD]Obs[/TD]
[TD]Date[/TD]
[TD]Obs[/TD]
[TD]Date[/TD]
[TD]Obs[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Fresh1[/TD]
[TD]22-May-18[/TD]
[TD]E Fresh1[/TD]
[TD]07-Aug-17[/TD]
[TD]E Fresh1[/TD]
[TD]07-Aug-17[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]0[/TD]
[TD]#NAME?[/TD]
[TD]E Fresh2[/TD]
[TD]04-Sep-17[/TD]
[TD="align: center"]#NAME?[/TD]
[TD]#NAME?[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: right"]0[/TD]
[TD]#NAME?[/TD]
[TD]E Fresh3[/TD]
[TD]02-Oct-17[/TD]
[TD="align: center"]#NAME?[/TD]
[TD]#NAME?[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: right"]0[/TD]
[TD]#NAME?[/TD]
[TD]E Fresh4[/TD]
[TD]24-Oct-17[/TD]
[TD="align: center"]#NAME?[/TD]
[TD]#NAME?[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD="align: right"]0[/TD]
[TD]#NAME?[/TD]
[TD]E Fresh5[/TD]
[TD]24-Nov-17[/TD]
[TD="align: center"]#NAME?[/TD]
[TD]#NAME?[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD="align: right"]0[/TD]
[TD]#NAME?[/TD]
[TD]E Seedhd1[/TD]
[TD]21-Dec-17[/TD]
[TD="align: center"]#NAME?[/TD]
[TD]#NAME?[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD="align: right"]0[/TD]
[TD]#NAME?[/TD]
[TD]E Fresh6[/TD]
[TD]08-Jan-18[/TD]
[TD="align: center"]#NAME?[/TD]
[TD]#NAME?[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD="align: right"]0[/TD]
[TD]#NAME?[/TD]
[TD]E Fresh7[/TD]
[TD]26-Feb-18[/TD]
[TD="align: center"]#NAME?[/TD]
[TD]#NAME?[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD="align: right"]0[/TD]
[TD]#NAME?[/TD]
[TD]Fresh8[/TD]
[TD]03-Apr-18[/TD]
[TD="align: center"]#NAME?[/TD]
[TD]#NAME?[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD="align: right"]0[/TD]
[TD]#NAME?[/TD]
[TD="align: right"]0[/TD]
[TD]#N/A[/TD]
[TD="align: center"]#NAME?[/TD]
[TD]#NAME?[/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="width: 687"]
<colgroup><col><col span="2"><col><col span="3"><col><col><col></colgroup><tbody>[TR]
[TD]F[/TD]
[TD]A[/TD]
[TD]N[/TD]
[TD]FLP[/TD]
[TD]Other[/TD]
[TD]Code[/TD]
[TD]No.[/TD]
[TD][/TD]
[TD]Trail No.[/TD]
[TD]Location[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]FLP[/TD]
[TD]1801[/TD]
[TD]FLP_1801[/TD]
[TD]FLP1801[/TD]
[TD]E3, Station[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD]FLP[/TD]
[TD]1802[/TD]
[TD]FLP_1802[/TD]
[TD]FLP1802[/TD]
[TD]E3, Station[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD]FFL[/TD]
[TD]1803[/TD]
[TD]FFL_1803[/TD]
[TD]FFL1803[/TD]
[TD]E3, Station[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD]FLH[/TD]
[TD]1805[/TD]
[TD]FLH_1805[/TD]
[TD]FLH1805[/TD]
[TD]E3, Station[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD]FLHT[/TD]
[TD]1806[/TD]
[TD]FLHT_1806[/TD]
[TD]FLHT1806[/TD]
[TD]E3, Station[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD]FLM[/TD]
[TD]1807[/TD]
[TD]FLM_1807[/TD]
[TD]FLM1807[/TD]
[TD]M, Station[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD]FLMT[/TD]
[TD]1808[/TD]
[TD]FLMT_1808[/TD]
[TD]FLMT1808[/TD]
[TD]M, Station[/TD]
[/TR]
</tbody>[/TABLE]