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.

Table that I want drag autofill top row according to list in column B

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.

WA49425 second question example

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?

1 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)


Working Formula in Spreadsheet for Horizontal Drag Auto-Fill

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

Finished Spreadsheet, it WORKS

Another alternative:


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.