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??

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

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.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"shelfish" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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??
>

Rows() & Columns() permit the formula to be entered *anywhere*.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Pete_UK" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

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