Iterative Lambda with indirect

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.