Iterative Lambda with indirect

[morphil]

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 Tab_Value headers. Ex :
Tab_Name       Tab_Value
Tab_CLPC_415   4.1.5
Tab_CLPC_413   4.1.3

Many Tab_CLPC_ijk Excel tables (whose names are contained in CLPC_Status[Tab_Name]). Each of these tables have one [Country] header and Product names such as [ProdA], [ProdB], headers. On each cell of those tables (for every given country and given product name, there is either:
2
0
or NA

Goal :
I need to build a table with the same structure as my excel tables above, but which calculates each cell as follow: It scans all Tab_CLPC_ijk looking for a 2 value in a given order. For example, if 2 was found on the 2nd table Tab_CLPC_413 (and not on the first one Tab_CLPC_415) it should returns a string (e.g 4.1.3) and stops. If no 2 value was found it should return an error value NA()
Here below is the way I did realize it until now
=IF(XLOOKUP([@COUNTRY],Tab_CLPC_415[COUNTRY],Tab_CLPC_415[ProdA],,0,1)=2,"4.1.5",
IF(XLOOKUP([@COUNTRY],Tab_CLPC_413[COUNTRY],Tab_CLPC_413[ProdA],,0,1)=2,"4.1.3",
IF(XLOOKUP([@COUNTRY],Tab_CLPC_405[COUNTRY],Tab_CLPC_405[ProdA],,0,1)=2,"4.0.5",
IF(XLOOKUP([@COUNTRY],Tab_CLPC_403[COUNTRY],Tab_CLPC_403[ProdA],,0,1)=2,"4.0.3",
IF(XLOOKUP([@COUNTRY],Tab_CLPC_326[COUNTRY],Tab_CLPC_326[ProdA],,0,1)=2,"3.2.6",
IF(XLOOKUP([@COUNTRY],Tab_CLPC_325[COUNTRY],Tab_CLPC_325[ProdA],,0,1)=2,"3.2.5",NA()))))
This works, BUT if a new Tab_CLPC_ijk comes, this needs to edit again these formulas which are not so easy to read and maintain

Thus the idea to use the CLPC_Status Excel table to get the table names from column CLPC_Status[Tab_Name]. And the return value from CLPC_Status[Tab_Value]. It looks a good candidate for an (iterative ?) LAMBDA function, using INDIRECT to address my Tab_CLPC_ijk tables but I did fail until now to make it work

Of course a VBA module could achieve this as well, but I dont like the fact to change the standard Workbook into a Workbook with macro
Thank you for your advises

[#2]

@morphil 

It would help us all help you if you could post a copy of your workbook on OneDrive or GoogleDrive, with a link pasted here that grants access.

In the absence of that, you're asking (in effect) that we create a formula in a vacuum OR create what we think your workbook looks like and then create that formula, in every case running the risk of not at all replicating your situation.

[#3]

@morphil 

The main challenge is that of combining your tables into one (it could be by replicating the Tables using PQ or building the table in memory using Lambda functions).  To combine the tables, I have converted them to an array of Thunks, which I then expand using REDUCE.  It was not obvious to me whether your tables have to same headers as one another or, even better, could be regarded as a 3D range. 

For the conversion, I defined a Lambda function Thunkλ defined by

Thunkλ
= LAMBDA(x, LAMBDA(x))

I then defined named Thunks to hold the tables and stacked them to form a named array.

Tab_CLPC_415ϑ
= Thunkλ(EXPAND(Tab_CLPC_415, , 4, "4.1.5"))

Tab_CLPC_413ϑ
= Thunkλ(EXPAND(Tab_CLPC_413, , 4, "4.1.3"))

Note: I added a column to hold the Table value.  A stacked table could then be returned using

Tab_Arrayϑ
= VSTACK(Tab_CLPC_415ϑ, Tab_CLPC_413ϑ)

= REDUCE(HSTACK(headers,"Value"), Tab_Arrayϑ,
    LAMBDA(stack,Tabϑ,
        VSTACK(stack, Tabϑ())
    )
  )

but I chose to continue with the "ProdA" lookups, so used

= LET(
    Tab_Stacked, REDUCE(HSTACK(headers,"Value"), Tab_Arrayϑ,
      LAMBDA(stack,Tabϑ,
        VSTACK(stack, Tabϑ())
      )
    ),
    Tab_Body, DROP(Tab_Stacked, 1),
    Country_Stacked, CHOOSECOLS(Tab_Body, 1),
    ProdA_Stacked,   CHOOSECOLS(Tab_Body, 2),
    Value_Stacked,   CHOOSECOLS(Tab_Body, 4),
    XLOOKUP(Country, IF(ProdA_Stacked=2, Country_Stacked), Value_Stacked)
  )

By now, you are probably wishing you hadn't asked the question and I cannot be sure I have answered it since there is guesswork involved in setting up the problem!

[#4]

@morphil 

If your could provide us a sample file without sensitive data. It will be much more helpful to solve your concern. Like Mr. @Peter Bartholomew here is my guess approach and suggestion to your work.

=LAMBDA(country,
    LET(
        tableNames, CLPC_Status[Tab_Name],
        tableValues, CLPC_Status[Tab_Value],
        numTables, ROWS(tableNames),
        result, NA(),
        i, 1,
        found, FALSE(),
        WHILE(i <= numTables,
            tableName, INDEX(tableNames, i),
            tableValue, INDEX(tableValues, i),
            tableRef, INDIRECT(tableName),
            productCol, INDEX(tableRef, 0, MATCH(country, INDEX(tableRef, 1, 0), 0)),
            hasTwo, IFERROR(MATCH(2, productCol, 0), 0),
            IF(hasTwo > 0,
                result = tableValue,
                i = i + 1
            ),
            found = (hasTwo > 0)
        ),
        IF(found, result, NA())
    )
)

This function takes a country as input and returns the CLPC value corresponding to the first table that contains a 2 in the column for that country, or NA if no such table is found.

Here's how the function works:

1. It first defines some variables using the LET function, including the list of table names, the list of corresponding CLPC values, and the number of tables.
2. It initializes some variables for use in the loop, including the result (which will store the final CLPC value), the loop index i, and a boolean variable found to keep track of whether a table with a 2 has been found.
3. It then enters a WHILE loop that will iterate through each table until a table with a 2 is found or all tables have been searched.
4. In each iteration of the loop, the function uses INDEX and INDIRECT to get a reference to the current table and the column for the input country.
5. It then uses MATCH to search for a 2 in the column and stores the result in the has Two variable.
6. If a 2 is found, the result variable is set to the corresponding CLPC value, and the loop is exited by setting i to a value greater than the number of tables.
7. If a 2 is not found, i is incremented to move on to the next table.
8. Finally, the function uses IF and the found variable to return either the final CLPC value or NA.

To use this function, simply enter it into a cell and provide a country as input. You can then copy the formula to other cells to calculate the CLPC value for other countries.

Note that the function assumes that all tables have the same structure, with the country names in the first column and the product names in the first row. If this is not the case, you may need to modify the function accordingly.

[#5]

@Rr_

Hello, here below is a link to download my example file

https://we.tl/t-TmrHQ453RD 

Best regards

[#6]

That link doesn't work for non-subscribers to the service. Can you post on GoogleDrive or OneDrive?

[#7]

Hi @morphil 

Until the LAMBDA experts suggest something more efficient...

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

[#8]

@morphil 

I combined your workbook and my named formulae

and things still seem to work.  If I were developing such solutions I would probably write further Lambda functions to break the formula into smaller parts and to encapsulate the detail that need not be exposed to an end user.

[#9]

@L z. 

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

[#10]

@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

[#11]

@L z. 

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 ...

[#12]

@morphil 

If you are able to implement the methods I provided, I would be interested to see the results regarding performance.  3000 cells is not a large number and should not cause problems unless you are performing expensive tasks involving the 3000 cells 3000 times (once for each cell).

[#13]

Hello @morphil 

Like I said I'm not a LAMBDA master + you asked for something generic

By the latter I understood this should work with 3 or 10 or 8 Products, hence the many XLOOKUP to return the appropriate arrays. If finally the number of Products is known/static there's probably something to do to improve the performances

Another option that I quickly experimented with > 200 countries & 15 products is Power Query. Let me know if you're interested

[#14]

Hello @L z.

I'm curious about your PQ solution. Does it deal with generating the HiddenArray only or also the Result table as well ?

With regards

[#15]

Hi @Peter Bartholomew 

I will try your suggestion, but as far as I could see, it was quite similar to l.z 's one

With regards

[#16]

Hello @morphil 

I'm curious about your PQ solution. Does it deal with generating the HiddenArray only or also the Result table as well ?

No hidden array nor anything similar and Yes the output is a Table. I'll share it later...

[#17 · Accepted solution]

@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)

[#18]

Hello @L z. 

Congratulations ! Your PQ solution is just amazing. The only drawback is that the query needs to be refreshed when a user make any modifications in the Tab_CLPC_ijk tables, but the refresh is almost instantaneaous

My complete goal is however slightly more complex. The complete equation I need has one more line (1) that acts as a global enable for the 3 following lines which you kindly realized through a PQ

I will try to modify your query in ordere to add that extra condition, and may be if I can't you will be kind enough to help me again

With Best Regards

[#19]

Hello @morphil 

You're finally far from an iterative lambda ;-)). Anyway, if you need help with Power Query I suggest you open a separate thread/discussion (ref. that one for background if you think this is important)

Best..