Are there such things as variables within an Excel formula?
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)
Now you can use the function LET to declare variables within Excel formulas. This function is available since Jun 2020 for Microsoft 365 users.
Given your example, the formula will be:
=LET(MyFunc,VLOOKUP(A1,B:B,1,0), IF(MyFunc > 10, MyFunc - 10, MyFunc ) )
The 1st argument is the variable name and the 2nd argument is the function or range. You can add more pairs of arguments variable, function/range.
After adding the variables, the last argument will be your formula of interest -- calling the variables you just created.
For more information, please access the Microsoft webpage here.