How to copy a vlookup formula without changing the "table_array" p

N

not 2 excell

I am trying to copy a vlookup formula to a whole column of cells with out the
formula changing the "table_array" part of the formula. When the formula is
copyed excell automaticaly changes the "lookup_value" to match the row number
that the cell is copied to. I want it to continue to do that without changing
the "table_array" part of the formula - Thanks in advance
 
T

Thomas

Hi there,

make the Table array's "absolute" (e.g. $c$1:$F8). Tip: Position your cursor
marker on e.g. C1 and press the F4 key, that' gives you $c$1 automatically)

Thomas
 
N

not 2 excell

Thomas,

Sounds good to me however I am not very excell savy. when I hit F4 it opend
a new workbook?? (I know it's me not you) the formula that I am using is
=VLOOKUP(A4,'PSI Data '!=VLOOKUP(A4,'PSI Data '!A2:Z2000,12,FALSE),12,FALSE).
I need the A2:Z2000 part to stay the same in each copied cell
 
N

not 2 excell

Thomas,

Ok !!! and thanks to you - By placing the $ sign in front of the
"table_array" reference cells and columns it works. Thanks again
 
C

Conan Kelly

not 2 excell,

What Thomas was talking about was when you are editing the formula.

If you go to a cell that already has a formula in it, hit [F2] (or
double-click the cell or click in the formula bar) to edit the formula. Now
position your cursor on a cell reference (for example: "=C4+D4" place the
cursor on C4 (between the = and the C, between the C and the 4, or between
the 4 and the +)). Now if you press [F4], it will change "C4" to "$C$4".
Press it again and you will get "C$4". Another press will get you "$C4".
One more press will get you back to "C4". Keep pressing it and it will
cycle through all of the possibilities of absolute and relative.

When you are editing a cell/formula, the [F4] key will cycle through
absolute/relative of a reference. But, if you are not editing a cell, [F4]
is Redo: it will repeat the last thing you did; or if you have just undone
a bunch of things in your file ([Ctrl] + Z or the Undo button on the
toolbar), each press of the [F4] key will Redo the next thing in the Redo
list. [F4] is equivalent to [Ctrl] + Y or clicking the Redo button on the
toolbar if you have undone some things.

I hope this helps,

Conan
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top