Functions

Functions can refer to built-in functions or custom functions (vba, xlm, xll, automation , js.) The primary tool for function information is the insert function dialog. Bold argument names indicate that the input is not optional. Without any input data, each argument shows a data type next to the input which may be a value (number, text, logical, any), a reference or an array type. Since arguments may consist of multiple types, an argument that shows a value as the data type may also take arrays or references and an argument that shows a reference type may also take arrays.

The evaluate formula tool breaks down the evaluation of a formula into calculation steps. Prior to evaluating the result, any arguments that require value conversion are underlined. If references are entered as inputs, only those arguments that are not reference types are converted to values when stepping through.

For example the arguments of XLOOKUP are shown in the function wizard as: any, reference, reference, any, number, number. Using the insert function wizard with {1} as inputs and then Stepping through formula evaluation with parenthesised references eg (A1) shows the argument or result that are array or reference types. Formatting arrays in bold and references in italics:

XLOOKUP( lookup_value , lookup_array , return_array , [if_not_found] , [match_mode] , [search_mode] )