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.
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
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!
Andrew,
you have to use absolute references for the table array.
.
