How do I copy a VLOOKUP into a column and keep a constant array?

[Bobby6901]

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?

 

[Lars-Åke Aspelin]

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

 

[Mike H]

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

 

[Niek Otten]

Option 1: use a defined name for the table (Insert>Name>Define)

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

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