using ADDRESS in VLOOKUP

I have a multiple foreign exchange (FX) master sheet from which I want to draw some summary information and put it in a separate summary worksheet according to various currencies; USD or EUR or JPY etc

The first 21 columns of the master file are the data columns.

To the right of the “21 data columns” I have a repeating blocks of 38 columns. Each of the different 38 column blocks pulls the same info from the first 21 columns but have to handle each currency separately.

What I want to do is grab only a few columns of info from the master sheet and put them in the summary worksheet.

I know how to find which column that USD is in by using a hlookup, so I can figure out my starting point. From there the vlookup table array will start 18 columns over. It will always start on the 5th line. So using the Address function I can figure out the upper left of the table array. I also know the array will be 21 columns wide from the starting point, and it will be 300 rows deep; so I can figure out the lower right address of the array. Each table array I need will be the same size but have a different starting point.

What I don’t know is
1. how to put all this into a Vlookup so it can read the addresses I have determined and
2. how to make it look in another worksheet.

So far I have tried the following;

=VLOOKUP(E4,ADDRESS(5,HLOOKUP(F1,'FX PL Calc'!1:3,ROW('FX PL Calc'!A3)-ROW('FX PL Calc'!A1)+1,FALSE)+17,1,1,"FX PL Calc")&":"&ADDRESS(300,HLOOKUP(F1,'FX PL Calc'!1:3,ROW('FX PL Calc'!A3)-ROW('FX PL Calc'!A1)+1,FALSE)+37,1,1),6,FALSE)

The addresses give the following
=VLOOKUP(E4,{"'FX PL Calc'!$AP$5"}&":"&{"$BJ$300"},6,FALSE)

When I take a look at the Function Arguments window it is the table array it can’t read.

I will do that. I think my description was a bit long winded. it will take a day or so to do so.

thanks for getting back so quickly

Turns out the following works very nicely

VLOOKUP($E5,INDIRECT(CONCATENATE(H$1,":",J$1)),6,FALSE))

where H$1 and J$1 are addresses using the ADDRESS function.

bebo021999, sincere thanks for your reply.

I knew I was close and just kept trying things until it finally took.

Again many thanks