Keep the same matrix - V lookup
Solved
Nanou
-
Nanou -
Nanou -
Hello,
I want to update data with two clicks and for that, I'm using VLOOKUP and I want to copy my formula while keeping the same reference table. I have a small issue, it’s not working!! I know I need to set my cells to absolute reference, but I can’t figure out how to do it!!!
Thank you for your help!
Julie
Configuration: Windows 2003 / Internet Explorer 6.0
I want to update data with two clicks and for that, I'm using VLOOKUP and I want to copy my formula while keeping the same reference table. I have a small issue, it’s not working!! I know I need to set my cells to absolute reference, but I can’t figure out how to do it!!!
Thank you for your help!
Julie
Configuration: Windows 2003 / Internet Explorer 6.0
5 réponses
Hello
example:
=VLOOKUP(A1;$B$1:$B$100;2;0)
the range B1:B100 is locked by the $ signs that you can place:
either by placing the cursor on the cell address in the formula bar and pressing F4 successively.
or by typing the $ sign on the right side of the keyboard.
$B$1 locks B1
$B1 locks B but not 1
B$1 locks 1 but not B
thanks
Edit:
you can also name the range, it clarifies the formulas
select B1:B100 for the example
Insert / Name / Define and place the name for the example FIELD
the formula becomes:
=VLOOKUP(A1;FIELD;2;0) it's the simplest.
Let's ask ourselves if we are not alone in understanding what we explain?
example:
=VLOOKUP(A1;$B$1:$B$100;2;0)
the range B1:B100 is locked by the $ signs that you can place:
either by placing the cursor on the cell address in the formula bar and pressing F4 successively.
or by typing the $ sign on the right side of the keyboard.
$B$1 locks B1
$B1 locks B but not 1
B$1 locks 1 but not B
thanks
Edit:
you can also name the range, it clarifies the formulas
select B1:B100 for the example
Insert / Name / Define and place the name for the example FIELD
the formula becomes:
=VLOOKUP(A1;FIELD;2;0) it's the simplest.
Let's ask ourselves if we are not alone in understanding what we explain?
Hello,
You need to use the sign $
Example: $A$1 for absolute value and $A1 or A$1 for relative value.
Have a nice day.
You need to use the sign $
Example: $A$1 for absolute value and $A1 or A$1 for relative value.
Have a nice day.
Thank you, and when it concerns a sheet??
=VLOOKUP(A2,Cumul!A:AA,4,FALSE)
I want to lock Cumul!A:AA and increment ;4; to ;5; ;6; etc...
=VLOOKUP(A2,Cumul!A:AA,4,FALSE)
I want to lock Cumul!A:AA and increment ;4; to ;5; ;6; etc...
Re
=VLOOKUP(A2,Cumul!$A:$AA,4,0) 0 or even just ;) is equivalent to FALSE
but you can also name the range, see Edit previous message.
Moreover, I believe you would benefit from limiting your range in rows to avoid an overly broad and heavy search.
As for the column number to edit, you can use depending on the direction of your increment the ROW() or COLUMN() codes which return the row or column number in the sheet references.
For example:
if your formula with ;4; is on row 4 replace ;4; with ;ROW();
this number increments as you drag the formula down
the same goes for COLUMN() for horizontal incrementing
If your number does not correspond to the line, you can make a correction:
for example with a value of 4 located on row 6 ;ROW()-2;
or also :ROW(A4) which will increment in the same way.
etc...
Best regards
--
Let’s ask ourselves if we are not alone in understanding what we explain?
=VLOOKUP(A2,Cumul!$A:$AA,4,0) 0 or even just ;) is equivalent to FALSE
but you can also name the range, see Edit previous message.
Moreover, I believe you would benefit from limiting your range in rows to avoid an overly broad and heavy search.
As for the column number to edit, you can use depending on the direction of your increment the ROW() or COLUMN() codes which return the row or column number in the sheet references.
For example:
if your formula with ;4; is on row 4 replace ;4; with ;ROW();
this number increments as you drag the formula down
the same goes for COLUMN() for horizontal incrementing
If your number does not correspond to the line, you can make a correction:
for example with a value of 4 located on row 6 ;ROW()-2;
or also :ROW(A4) which will increment in the same way.
etc...
Best regards
--
Let’s ask ourselves if we are not alone in understanding what we explain?