I am trying to copy a VOOKUP formula down a column, and
Excel wants to change the reference array as I move to lower rows. How do I
stop that?

On Sat, 26 Jul 2008 08:15:01 -0700, Bobby6901
wrote:

I am trying to copy a VOOKUP formula down a column, and
Excel wants to change the reference array as I move to lower rows. How do I
stop that?


Use $ in the reference of your lookup vector, like this

=VLOOKUP(C1,A$1:B$10, 2)

Hope this helps / Lars-Åke

Hi,

Use an absolute reference

=VLOOKUP(C1,$A$1:$B$17,2,FALSE)

The $ signs Fix the range and are easilly entered by tapping F4 as you enter
the range into the formula.

Mike


"Bobby6901" wrote:

I am trying to copy a VOOKUP formula down a column, and
Excel wants to change the reference array as I move to lower rows. How do I
stop that?

Option 1: use a defined name for the table (InsertNameDefine)

Option 2: Use absolute references instead of relative.

Not:

=VLOOKUP(C1,A1:B3,2,FALSE)

but

=VLOOKUP(C1,$A$1:$B$3,2,FALSE)

You can toggle between different variations of absolute and relative with the F4 key, when the cursor is on the reference in the
formula bar

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Bobby6901" wrote in message ...
|I am trying to copy a VOOKUP formula down a column, and
| Excel wants to change the reference array as I move to lower rows. How do I
| stop that?

Hey there! I can definitely help you with that.

When you copy a VLOOKUP formula down a column, Excel automatically adjusts the reference array to match the row you're copying it to. However, if you want to keep the reference array constant, you can use what's called an absolute reference.

Here's how you can do it:

1. Select the cell with the VLOOKUP formula you want to copy.
2. Click on the formula bar at the top of the screen to edit the formula.
3. Highlight the reference array (the range of cells you're looking up from) in the formula.
4. Press the F4 key on your keyboard. This will add dollar signs ($) to the reference array, making it an absolute reference.
5. Press Enter to save the formula.
6. Copy the cell with the VLOOKUP formula.
7. Paste the formula into the cells below it.

Now, when you paste the formula into the cells below it, the reference array will stay constant, and Excel won't adjust it as you move down the column.

I hope that helps!