SQL set of functions in Excel - MIght be interesting

Question

Hi people, interested about high-piloting in Excel

I just finished (actually there is no finish - countinues fun process)
close to full set of relational functions in Excel based only on regular functions and lambdas

Possible example

=λWhere(

λSelect(

λWhere(

    λSQL("select ProductName; CategoryName;unitprice;unitsinstock from products join categories using categoryid order by unitprice desc");

    "CategoryName";"Beverages");;

       "Laoseis";

       "Unitprice;Unitsinstock";

       LAMBDA(unitprice;unitsinstock;unitprice*unitsinstock));

   "Laoseis";

   LAMBDA(laoseis;laoseis < 700)
```)  
  
there is λFrom, λSelect, λOrder, λWhere and finally native-like λSQL  
  
λFrom supports filename (importcsv based), tablenames, ranges, spilled tables (HD-format)  
λSelect support calculated projection columns (actually one column, but extending)  
λWhere support scalar, list and lambda predicates  
  
When interesting - just contact me ([Moderator note: personal info removed])  

no selling advert - just fun to share and discuss  
  
no very street level topic (but usable for end/advanced users)

    
  
  

Answer 1

Dear Henn Sarv,

Thank you very much for your contribution and for taking the time to share your findings. The information you provided is highly valuable and can be beneficial to many users who may encounter similar situations.

To further extend the reach and usefulness of this information, you can also create a post on the Microsoft Tech Community platform. This is a public forum designed for a wide range of users, including IT professionals, developers, and power users working with technologies such as Excel VBA, SQL, and other Microsoft solutions. The platform encourages collaboration and knowledge sharing, allowing community members to learn from one another, exchange best practices, and collectively improve their workflows and technical skills. Posting your experience there could help others facing similar challenges and may also attract insights or recommendations from subject‑matter experts.

When creating your post, please ensure that no personally identifiable information (PII) or sensitive data is included. This includes, but is not limited to, usernames, email addresses, tenant details, internal URLs, or any confidential business information. Adhering to these privacy and security best practices helps protect all users and ensures compliance with Microsoft community guidelines.

Once again, thank you for your valuable contribution and willingness to support the broader community.

Answer 2

Excel now has several advanced capabilities that align well with the kind of relational/LAMBDA-based framework described.

1. Custom LAMBDA-based functions

• LAMBDA functions let users build reusable, parameterized logic directly in Excel without VBA or JavaScript.
• They can be combined with dynamic array functions and named ranges to simulate higher-level constructs such as λFrom, λSelect, λWhere, and λOrder.
• Certain array helpers like BYROW, BYCOL, MAP, and MAKEARRAY are specifically designed to work with LAMBDAs and can help implement row-wise and column-wise transformations similar to SQL SELECT and WHERE operations.

2. Custom functions add-ins + LAMBDA

• Custom functions from an Office Add-in can be used as the LAMBDA parameter in:
  • BYCOL
  • BYROW
  • MAKEARRAY
  • MAP
• This allows building hybrid solutions where some logic is implemented in JavaScript add-ins and some in native LAMBDA formulas, which can resemble a relational/SQL-like layer on top of Excel data.

3. Power Query and Power Pivot as relational layers

• Power Query custom functions (created via the Advanced Editor) provide another functional layer for data shaping, including custom M functions that can be invoked over tables, similar in spirit to λFrom and λWhere.
• Power Pivot with DAX provides a relational data model inside Excel, supporting relationships, calculated columns, and measures over large datasets, which is conceptually close to SQL-style querying.

Overall, building a relational abstraction over Excel using LAMBDA and dynamic arrays is consistent with the direction of recent Excel features and can complement existing tools like Power Query and Power Pivot for advanced users.

---

References:

• Use custom functions with LAMBDA functions
• Import and query data using the Azure Databricks Excel Add-in
• Create and invoke a custom function
• Power Pivot: Powerful data analysis and data modeling in Excel
• Excel as a data source - HIS
• Excel performance: Performance and limit improvements