How to manipulate the Excel feature "Drag outside selection to extend series or fill..."?

Question

Here's the formula I'm trying to extend. It makes it's own sequence and has it's own idea of the pattern...is there a way to manipulate the pattern so it's changing the correct formula values?

Cell B3=VLOOKUP(B2,'Copy of Book2'!A2:B19,2,)
Cell B4==VLOOKUP(B2,'Copy of Book2'!A2:C19,3,)

It adds 1 additional column to the table array, and 1 to the column index number.

Excel tries to move the table array down 1 cell since that's where I've gone...I've tried making up to 4 or 5 of the proper formula manually, and it still doesn't pick up on the pattern.

Suggestions?

expletive_xom · Accepted Answer

Hi Steven,
you did ask the question twice, but you seem to have intentionally reworded the question...so posting twice was no accident.

that said, its a good question.
- you dont say how many columns you want....you mention moving from B to C....so i will just use Z.
- you want to use the $ in the formula to prevent Excel from taking it upon itself to change your formula as you drag down.
- since you want to increase the column index by 1...you want to take advantage of the ROW() function.  for future reference...if you put this formula in any cell =ROW(A1) and drag down, then you will see a pattern.  then you can use basic math like =ROW(A1)*2 to start getting patterns you will need in your future formulas that need patterns.

i think this is what you want

=VLOOKUP($B$2,Copy of Book2!$A$2:$Z$19,ROW(A2),)

then drag down.

since you have the ' around Copy of Book 2...you might be using 2 files (not just 2 sheets in the same file). so maybe this.

=VLOOKUP($B$2,'Copy of Book2'!$A$2:$Y$19,ROW(A2),)

let me know how it works.

Anonymous · Answer

Excel is crap.

Trending News

How to manipulate the Excel feature "Drag outside selection to extend series or fill..."?

2 Answers