Auto increment the data range in Vlookup

[anglais428]

Hi,

I am looking to automatically increase the data range inside a vlookup formula.

I have various tables in the same sheet beneath each other. Each table has the same no of columns and rows, only the data is different.

The first table goes from B6:D15, table 2 goes from B22:D31, table 3 from D38:D47 and so on.

In another sheet I want to re-organise and have a vlookup in place to pull the information:

=$A2,Sheet!1B6:D15,3,false)

where A2 is a country, found in Column B of the first sheet
And Column D holds the data I am looking for.

I want to be able to auto increment the "Sheet!1B6:D15 each time by 16 so when I copy across the cell beside will have =$A2,Sheet!1B22:D31,3,false)

Was thinking use of ROWS could be the solution?

Thanks

 

[Robert Mika]

=VLOOKUP($A2,INDIRECT("'Sheet1'!"&"B"&6+(COLUMNS($A$1:A1)-1)*16&":"&"D"&15+(COLUMNS($A$1:A1)-1)*16),3,FALSE)

 

[anglais428]

This is awesome. Many thanks