Autofill by Dragging with Vlookup (multiple cells) - Help please :D

[henryhwong]

I have this:
First Cell =VLOOKUP(A12,'Acme'!A6:B$500,2,FALSE)
Second Cell =VLOOKUP(A13,'Acme'!A6:B$500,2,FALSE)
Third Cell =VLOOKUP(A14,'Acme'!A7:B$500,2,FALSE)
Fourth Cell =VLOOKUP(A15,'Acme'!A7:B$500,2,FALSE)


I'd like:
Fifth Cell = =VLOOKUP(A16,'Acme'!A8:B$500,2,FALSE)
Sixth Cell = =VLOOKUP(A17,'Acme'!A8:B$500,2,FALSE)

by just dragging on the corner of the box. I'm not sure how to do that?

I tried selecting the first 4 cells, hoping it would identify the trend, if it is was one cell at a time, no problem, but i'd like to drag this for hundreds of records and its not following the same structure.

Can someone please teach me how to drag and autofill correct to get the results i'm looking for? I also tried copy/pasting hoping those values would transfer ok, but that doesnt work either.


thank you!

 

[tybaltlives]

Here's a way to do what you want using INDIRECT and a helper column of numbers (column C in the example below). You can drag the helper numbers down as far as you need. They complete the range you need. The search value drags normally. The equation in column B drags normally.

By normaly, just select the lowest formula in the cell and look for a small solid square at the lower left of the selection. your cursor should change to a plus sign. Then just click on that rectangle once and hold while dragging the formula down. You would need to drag the formula in B and C down as far as you need to go. I showed the values on the "Acme" tab for reference starting in A20 but you dont need this table on the Sheet1 tab.

Excel 2010
	A	B	C
1		10	6
2		20	6
3		30	7
4		40	7
5		50	8
6		60	8
7			9
8			9
9			10
10			10
11			11
12	a		11
13	b		12
14	c		12
15	d
16	e
17	f
18
19
20	Values in Acme starting in A6
21	a	10
22	b	20
23	c	30
24	d	40
25	e	50
26	f	60
Sheet1

Cell Formulas
Range	Formula
B1	=VLOOKUP(A12,INDIRECT("Acme!A"&C1&":B$500"),2,FALSE)
B2	=VLOOKUP(A13,INDIRECT("Acme!A"&C2&":B$500"),2,FALSE)
B3	=VLOOKUP(A14,INDIRECT("Acme!A"&C3&":B$500"),2,FALSE)
B4	=VLOOKUP(A15,INDIRECT("Acme!A"&C4&":B$500"),2,FALSE)
B5	=VLOOKUP(A16,INDIRECT("Acme!A"&C5&":B$500"),2,FALSE)
B6	=VLOOKUP(A17,INDIRECT("Acme!A"&C6&":B$500"),2,FALSE)
C3	=C1+1
C4	=C2+1
C5	=C3+1
C6	=C4+1
C7	=C5+1
C8	=C6+1
C9	=C7+1
C10	=C8+1
C11	=C9+1
C12	=C10+1
C13	=C11+1
C14	=C12+1

 

[ukmikeb]

Hi

Welcome to the MrExcel Forum.

Without a helper column try :-

=VLOOKUP(A12,INDIRECT("Acme!A"&6+INT((ROWS($1:1)-1)/2))&":B$500"),2,FALSE)

and drag down.

hth

 

[henryhwong]

Wow, thanks guys!

I tried these, but its giving me an error. I'm using Excel 2013.

I'm trying to reference another Sheet/Tab so isn't the " ' " necessary in there as originally shown? It's also saying "You've entered too few arguments for this function".

I do understand what you are trying to do and will give it a few more tries. If you have a resolution, i'd appreciate it so much! I've been trying forever :D

so helpful, this community, really awesome!

 

[henryhwong]

ahhh, i got it!!

there was a missing "(" in front of the 6.

and i still needed the original " ' " for sheet reference. but it worked brilliantly!! the INT value was smart

thank you!

 

[henryhwong]

I have a new problem

once i get past line 1000, it's not working anymore... must be something with the INT formula?

 

[henryhwong]

NEVERMIND! I GOT IT! WOW you guys are great!

I did realize something though. For lots of rows, these equations become very resource intensive on my laptop. With 2GB i had to move to my computer.

Any ideas on how why Excel becomes so resource intensive and if its the way i'm using Excel, or is this standard for most people (I have plenty of RAM on my desktop)

 

[ukmikeb]

Hi

I have heard that 2013 can become a bit greedy!

Although I wouldn't have thought that 1000 rows would cause too much of a problem. How many columns are you copying the formula across.

It's quite easy to upgrade memory on a laptop.

Pleased to have helped solve your problem.

Good luck with your project.

 

[tybaltlives]

I use it a lot, but INDIRECT is resource intensive if you have a lot of them. Sometimes its best to copy and paste formulas as values if you no longer need the function. But other times you cant do this. Just thought I would suggest it.