You could define a name for the VLOOKUP part of the formula.
- Highlight the cell that contains this formula
- On the Insert menu, go Name, and click Define
- Enter a name for your variable (e.g. ‘Value’)
- In the Refers To box, enter your VLOOKUP formula:
=VLOOKUP(A1,B:B, 1, 0) - Click Add, and close the dialog
- In your original formula, replace the VLOOKUP parts with the name you just defined:
=IF( Value > 10, Value - 10, Value )
Step (1) is important here: I guess on the second row, you want Excel to use VLOOKUP(A2,B:B, 1, 0), the third row VLOOKUP(A3,B:B, 1, 0), etc. Step (4) achieves this by using relative references (A1 and B:B), not absolute references ($A$1 and $B:$B).
Note:
For newer Excel versions with the ribbon, go to Formulas ribbon -> Define Name. It’s the same after that. Also, to use your name, you can do “Use in Formula”, right under Define Name, while editing the formula, or else start typing it, and Excel will suggest the name (credits: Michael Rusch)
Shortened steps: 1. Right click a cell and click Define name… 2. Enter a name and the formula which you want to associate with that name/local variable 3. Use variable (credits: Jens Bodal)