I understand your frustration with the VLOOKUP formula changing the table array when you copy and paste it. Fortunately, there is a simple solution to this problem.
Instead of using absolute cell references (i.e. A1, G1:H7), we can use mixed cell references (i.e. $A1, $G$1:$H$7) to lock the table array in place while allowing the lookup value to change as we copy and paste the formula.
Here's how to do it:
- Select the cell with the VLOOKUP formula (C1 in your example).
- Click on the formula bar at the top of the screen to edit the formula.
- Highlight the table array (G1:H7 in your example).
- Press the F4 key on your keyboard. This will add dollar signs ($) to the cell references, making them mixed cell references.
- The table array should now look like $G$1:$H$7. Press Enter to save the formula.
- Copy the formula from C1 and paste it into C2 and C3.
- The table array should remain locked in place while the lookup value changes as you copy and paste the formula.
Using mixed cell references is a great way to ensure that your formulas stay consistent as you copy and paste them throughout your worksheet.