Drag autofill of a multi-column pattern

Question

I'm trying to drag-copy some cells that are in the top row as headers. They get their values from a VLOOKUP formula (basicaly all group names are in column B and I want them as column headers). Before each cell there is another cell just containing string ID.

What I want is to select first pair (ID and Machinery) and drag to the right so that this pattern continues for all group names in column B.

It doesn't work. I thought selecting the first four cells (ID Machinery ID Clothing) and dragging them might help Google Spreadsheets recognize a pattern, but it didn't. I tried the same thing in Excel and if fails there too.

Then I realized dragging right only changes column letters in formulae not row numbers (which makes sense).

Is it possible to change this behaviour?

If not, any suggestions for me not to have to copy paste and edit for each entry?

Answer 1

As explained here when drag copy formula horizontally incrementation happens with anything related to columns. Nothing happens with rows. In order to get an increasing number one can use COLUMN() function in this scenario.

We get row number of original list with a calculation involving COLUMN() function, but then we have to create a string defining our original list's range and get the value in that cell. This is done by INDIRECT() function. (VLOOKUP method not good)

=INDIRECT("$B"&COLUMN()/2)

Now selecting ID and Machinery and dragging right fills all consecutive columns as desired.

Another alternative:

=INDEX($B:$B;COLUMN()/2)