Forum Discussion

Andrew Mesplay's avatar
Andrew Mesplay
Copper Contributor
May 10, 2018

VLOOKUP

I'm working with a workbook that has multiple sheets. I have several VLOOKUP formulas that work fine, but something odd happens when I attempt to copy the formula to other cells; the same value is copied to the new row even though the formulas has updated. What am I missing? Hear are some examples.

 

This is the first cell with correct results.This is after I copied the formula to the low cells.

vlookup
  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    May 10, 2018

    Hi Andrew,

     

    You have to do two things:

     

    1. Replace the formula in cell B1 with this:

    =VLOOKUP(A1,ASW!$A$51:$C$55,3,FALSE)

    This is will lock the table array so that it doesn't move when you copy the formula down.

     

    2. Go to Formulas >> Calculation >> Calculation Options, and check if the Calculation Option is set to Automatic.

     

     

    I hope this helps you

    Regards

    • Andrew Mesplay's avatar
      Andrew Mesplay
      Copper Contributor
      May 10, 2018

      Thank you for the clarification. My data-sets are not very large, so I can use either absolute, or relative references. Thanks for the tip on the settings. I don't know how it was changed to Manual. Everything works great! Thanks again!

Resources