Forum Discussion

morphil's avatar
morphil
Copper Contributor
Apr 23, 2023
Solved

Iterative Lambda with indirect

Hi everybody, I would appreciate your help in order to build an Iterative and generic Lambda with indirect to replace basic Nested If formula Inputs : One CLPC_Status Excel table with Tab_Name and...
excel
Formulas and Functions
office 365
  • Lorenzo's avatar
    Lorenzo
    Apr 27, 2023

    morphil 

    Power Query option attached

    Note that the 1st Refresh within an Excel session takes more time (time for the .Net Framework to load) that the next Refresh(es)

Lorenzo's avatar
Lorenzo
Silver Contributor
Apr 24, 2023

Hi morphil 

 

Until the LAMBDA experts suggest something more efficient...

With an Helper sheet to hide + a couple of defined names => in attached file

  • morphil's avatar
    morphil
    Copper Contributor
    Apr 24, 2023

    Lorenzo 

    Clever!

    If I understand properly you first merge all Tab_CLPC_ijk in the HiddenArray with an added column containing the return values i.j.k.

    Then you filter this HiddenArray for every given country and product and look for the first '2' value to return the first i.j.k

    Many thanks

    • Lorenzo's avatar
      Lorenzo
      Silver Contributor
      Apr 25, 2023

      morphil 

      You got it (I wouldn't use the word 'merge' though)

      Note that I updated the HiddenHeader defined name in the meantime => Please re-download the Example workbook from my previous post

      • morphil's avatar
        morphil
        Copper Contributor
        Apr 26, 2023

        Lorenzo 

        Hello. Although your solution works fine, it did slow down dramatically the workbook. In my case, I have 204 countries and 15 products thus 3060 cells to be evaluated in the Result Workshet.

        I hope that the #HiddenArray is evaluated only once as it is common to all formulas in the 3060 cells but I'm not sure ...

Resources