Forum Discussion
Iterative Lambda with indirect
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
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
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 ...
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
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).
I will try your suggestion, but as far as I could see, it was quite similar to l.z 's one
With regards
