The old way
Define a custom function. That means learn python or vba and their api.
The google sheets way
Define a named function. A little Dialog pops up. You get the same Formular Editor as you would use for cells. Additionally you can add parameters which you can use inside the formula. You save and from there on you can use this named function inside the sheet. What I also like is that you have a multiline Editor where you can indent the formula.
It turns this
“=INDEX(Sheet!$BX$5:$BX$9, MATCH(A1,Sheet!$BU$5:$BU$9, 0), 1)”
The function would be defined like this
Named Function: MY_LOOKUP, value
Named Function Body: “=INDEX(Sheet!$BX$5:$BX$9, MATCH(value,Sheet!$BU$5:$BU$9, 0), 1)”
- It is way easier to use than fiddling with vba
- lowers the barrier of entry for custom functions significantly
- easier access to sheet ranges (no api, just write formulas as you are used to)
- encourage cleaner formulas, makes everything more readable and structured
- makes functional programming feasible
This little tweak makes calc so much more powerful.