Auto increment the data range in Vlookup

anglais428

Well-known Member
Joined
Nov 23, 2009
Messages
634
Office Version
  1. 2016
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Robert Mika

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
=VLOOKUP($A2,INDIRECT("'Sheet1'!"&"B"&6+(COLUMNS($A$1:A1)-1)*16&":"&"D"&15+(COLUMNS($A$1:A1)-1)*16),3,FALSE)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,530
Messages
5,602,200
Members
414,513
Latest member
junbuggle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top