Hi Cynthtia,

I don't think there's a way to automatically up date the column number
as you copy across the sheet - it's just a number as far as Excel is
concerned and thus doesn't increment.

However you need to make the range refrences absolute befor you copy,
otherwise they will increment. Change your formula so they read $H
$39:$L$1000, and also for the first cell reference in the formula.

Actually, there are a couple of tricks that will at least reduce your
editing....

1 - In the first formula cell, highlight the range reference cells in
the formula bar, then press F4 (This will add the dollar signs). The
formula should look something like
=VLOOKUP($A$1,$H$1:$L$1000,5,FALSE), with your own cell references in,
of course!

Press Enter.

2 - Use the Autofill handle to copy the formula to the rest of the
row, then edit the colum numbers manually (a pain I know, but it's the
only manual editing you'll need to do!)

3 - When finished, highlight the entire row containing the formulas
and use the Autofill handle on the last highlighted cell and copy down
as far as you need. This will copy the lot.

4. Use Search/replace to edit the $ signs out of the first cell
reference in the formulae (the $A$1) to make it $A1.

And that should do it all a bit quicker!

Hope that helps,

Phil