Iterative Lambda with indirect

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.

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 

If your could provide us a sample file without sensitive data. It will be much more helpful to solve your concern. Like Mr. PeterBartholomew1 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.

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!

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.