I have a challenge which I can’t solve. Already tried multiple ways – makearray, byrow, etc. Nothing worked so far. Here is the challenge:

I have a large set of data (>20000 rows, dynamic). It holds a.o. segments and size of segment (a number). Let’s assume segment is in array A1# and size of segment is in array B1#. In a different table (~20 rows), all the unique segments are listed (named “Segments”). Next to this segment list, I have organized data for 3 categories (small/medium/large) for each segment. Each segment will have its own data for what is defined as small/medium/large. As such, I have 3 columns next to the segment name that holds number values for size thresholds.

In the large table, I now want to categorize each segment. Every segment (A1#) has a size (B1#) which corresponds to a category (small/medium/large) -> I.e. it needs a lookup in the category table.

I would want the formula to result in a spilled array – given the data is dynamic in nature.

Thanks for your ideas!

​I have a challenge which I can’t solve. Already tried multiple ways – makearray, byrow, etc. Nothing worked so far. Here is the challenge:I have a large set of data (>20000 rows, dynamic). It holds a.o. segments and size of segment (a number). Let’s assume segment is in array A1# and size of segment is in array B1#. In a different table (~20 rows), all the unique segments are listed (named “Segments”). Next to this segment list, I have organized data for 3 categories (small/medium/large) for each segment. Each segment will have its own data for what is defined as small/medium/large. As such, I have 3 columns next to the segment name that holds number values for size thresholds.In the large table, I now want to categorize each segment. Every segment (A1#) has a size (B1#) which corresponds to a category (small/medium/large) -> I.e. it needs a lookup in the category table.I would want the formula to result in a spilled array – given the data is dynamic in nature.Thanks for your ideas!  Read More

​