A fundamental library of Excel functions written in Excel formula language by LAMBDA function for spreadsheet automation
AutoXL is a fundamental and powerful library of Excel functions written in Excel formula language. It especially enables Excel users to automate manual tasks, by extensions of lookup and references functions, and elementary functions for array and set, etc. It thus saves Excel users lots of time, and avoids tedious and error-prone manual operations.
At the moment, AutoXL consists of 29 functions mainly in 3 categories:
A.LOCATE.CELLBYTEXT
to find a cell in a range that contains a given text.A.LOCATE.RANGEBYTEXT
to find a header in a range that contains a given text and to return the data below the headerA.DUPLICATES
to find duplicates in a rangeA.XLOOKUP.ROWS
and A.XLOOKUP.COLS
for built-in XLOOKUP
A.XMATCH.ROWS
and A.XMATCH.COLS
for built-in XMATCH
A.UNION.CELLS
, A.INTERSECT.CELLS
, A.SETDIFF.CELLS
for setA.EQ
for arrayAs manual operations in Excel exist everywhere, AutoXL is cross-sectors. It will mainly benefit
VLOOKUP
and are willing to try moreExcel version requirement: Many functions of AutoXL are written with newly-introduced built-in functions of Excel, which require Microsoft 365 and probably don't exist in non-subscription Office 2019 or later. Therefore, AutoXL has the same requirement. You could simply type =VSTACK
in a cell, if its intellisense shows up, that means your Excel meets the requirement.
Get started quickly: You could download the workbook Demo/AutoXL-Demo.xlsx
where AutoXL has been already added. It also contains sample data and formulas which allow you to get familiar with use of functions. Formula Editor will be auto-opened.
Installation: Besides using Microsoft's AFE, you could use Formula Editor to add the AutoXL library to your workbook, which will provide a version control. The latest stable versions of AutoXL will always be available in Formula Editor.
Uninstallation: To remove the library from your workbook,
A.
(make sure that you don't have other user-defined functions or ranged names starting with A.
)The documentation on a website is coming soon. At the moment, you could refer to the comments in the file AutoXL.txt
to see the list of the functions, their purpose, their arguments, etc.
Additionally, here are related built-in functions of Excel:
AutoXL is MIT licensed.
The design and implementation of AutoXL has the following principles:
The AutoXL project welcomes your expertise and enthusiasm! Contributions include:
You could open an issue or write to [email protected].
A.VERSION
: return the name and version of the libraryA.EQ
: return whether two values or arrays are equalA.XMATCH.ROWS
(similarly for A.XMATCH.COLS
): search for a specified row in an array row by row, and then return the row's relative positionA.XLOOKUP.ROWS
(similarly for A.XLOOKUP.COLS
): search an array row by row for a match with a given row and return the corresponding item from a second arrayA.REDUCE.ROWS
(similarly for A.REDUCE.COLS
): reduce an array to an accumulated value by applying a LAMBDA function to each row and returning the total value in the accumulatorA.SCAN.ROWS
(similarly for A.SCAN.COLS
): scan an array row by row by applying a function to each row, and return an array that has each intermediate valueA.UNION.CELLS
: find the union of two arrays by cells; return an array of the unique cells that are in either of the two input arraysA.UNION.ROWS
(similarly for A.UNION.COLS
): find the union of two arrays by rows; return an array of the unique rows that are in either of the two input arraysA.INTERSECT.CELLS
: find the intersection of two arrays by cells; return an array of the unique cells that are in both of the two input arraysA.INTERSECT.ROWS
(similarly for A.INTERSECT.COLS
): find the intersection of two arrays by rows; return an array of the unique rows that are in both of the two input arraysA.SETDIFF.CELLS
: find the set difference of two arrays by cells; return an array of the unique cells in one array that are not in the otherA.SETDIFF.ROWS
(similarly for A.SETDIFF.ROWS
): find the set difference of two arrays by rows; return an array of the unique rows in one array that are not in the otherA.COUNTEQ.ROWS
(similarly for A.COUNTEQ.COLS
): count the number of rows within an array that is equal to the given rowA.DUPLICATED.BYTIMES
: values that occur a given number of times in an array are indicated as TRUE in the resulting array.A.DUPLICATED
: values that occur a given number of times in an array are indicated as TRUE in the resulting array.A.DUPLICATES.BYTIMES
: search duplicated values by occurrence times in an array and return the corresponding item from a second arrayA.DUPLICATES
: search duplicated values in an array and return the corresponding item from a second arrayA.LOCATE.CELLBYTEXT
: return a reference to the first cell within an array that contains a given textA.JUMP
: locate a range by jumping from an origin range in a direction within a scope range to special cells, and return a reference to that rangeA.EXTEND
: locate a range by an origin range and the direction to jump within a scope range, and return a reference to that rangeA.LOCATE.RANGEBYTEXT
: find the first cell within an array that contains a text, extend it in a direction within a scope range to obtain a range, and return a reference to that range