Auto-Fill problem

S

shelfish

I use a lot of H/V- lookup. One of the problems I run into is that the
auto-fill doesn't change the individual number references to rows or
columns, only cell references. To get around this I usually hide on the
page I'm working a simple column that counts 1 to whatever and then
reference that. Is there a better way??
 
P

Pete_UK

If you have a formula which goes across, you can use the COLUMN()
function, like so:

=VLOOKUP(A1,Sheet1!$A$1:$M$100,COLUMN(),FALSE)

This would be entered in B1 of Sheet2, for example, and can be copied
across to column M to return in each cell the corresponding column's
data from the exact match of A1 with a lookup table of A1:M100 in
Sheet1.

A similar function acting vertically is ROW().

Hope this helps.

Pete
 
R

Ragdyer

Two very good functions to use for this "auto-incrementing" are:
Rows()
Columns()
Note the plural (s).

Enter
=Rows($1:1)
and drag down.

=Columns($A:A)
and drag across.

See what you get?

So ...
=Vlookup(A1,$B$1:$F$100,Rows($1:2),0)
Dragged down will increment the column to return.
Note: You *don't* have to *start* with "$1:1".

Same goes for columns.
 
S

shelfish

I do believe will do it. I should have asked this question years ago.
Thanks for the help.
 
Top