Forum Discussion
PeterBartholomew1
Sep 27, 2020Silver Contributor
Would a FOR.EACH function be useful in Excel 365?
The attached files are based upon a workbook that emulates the proposed function using a VBA macro. There would appear to be many potential applications in situations where the @operator needs to be...
PeterBartholomew1
Oct 01, 2020Silver Contributor
Still working. the present sheet was written as a demonstration of LET relevant to a question on Chandoo. The last entry for each field of a table was returned using XLOOKUP. It is not possible to search multiple rows within a single array formula, so the column is picked up as a relative reference.
Wrapping the formula within FOR.EACH removes the need for relative referencing and returns the results as a dynamic array.
FOREACH replaces relative referencing
Whereas the previous workbooks were created to demonstrate and idea and put it forward for discussion, this time I simply built the formula around an existing solution. The implementation of the macro is still weak but the idea seems to work well.