View original (French)

Keep the same matrix - V lookup

Solved
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

5 answers

  1.
    Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
     
    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?
    9
  2. g Posted messages 1285 Status Member 578
     
    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.
    0
  3. Nanou
     
    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...
    0
  4. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
     
    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?
    0
  6. Nanou
     
    Too STRONG !!

    Thank you very much, have a great day !!!
    0