Autofill with VLOOKUP

B

Brian Q. Lehmann

I finally got the VLOOKUP function to work for me, and now
it won't play nice with the Autofill functionality. I have
a cell equal to:
=VLOOKUP(A391,Prices!A25:M500,2,TRUE)*VLOOKUP(A391,Shares!
A2:T95,5,TRUE)

and when I drag the auto fill handle down, I'd like only
the A391 to increment. Instead, Autofill also increments
the table_arrays "Prices!A25:M500" to "Prices!A26:M501"
and "Shares!A2:T95" to "Shares!A3:T96". That doesn't make
sense since my tables have remained stationary. I've tried
customizing an autofill list but can't get that to help.

It's a cumbersome formula that I want repeated hundreds of
times in several places, so typing over those areas of the
auto-filled formula isn't practical.

Any tips?

Thanks, Brian
 
L

L. Howard Kittle

Hi Brian,

You need to set the table references to absolute. Then they won't change
when you fill down.

=VLOOKUP(A391,Prices!$A$25:$M$500,2,TRUE)*VLOOKUP(A391,Shares!$A$2:$T$95,5,T
RUE)

HTH
Regards,
Howard
 
Top