ROW() inside OFFSET() inside IF() not expanding array always!

I really do not understand what you are exploring.
If you want to display results for "NO OFFSET" is {0;"";"";3} , use your formulas:

Formula:

Please Login or Register  to view this content.

Ctrl + Shift + Enter!
If you want to display results for "WITH OFFSET" is {"Apple";"";"";"Dog"} , use this formulas:

Formula:

Please Login or Register  to view this content.

Ctrl + Shift + Enter!
You can use F9 to check it!

Tamthat, thanks for your response. Your method will work for the example I gave. But for what I need, the OFFSET function is necessary. I should have provided a better example. I will update the link in the morning.

The reason is because OFFSET allows you to not be restricted by 1:1 mapping. For instance, you can transform a 3x3 matrix into a 9x1 column. In my case, I'm needing transformations that aren't 1:1. So under "WITH OFFSET", but before apply the IF logic, if I wanted {"Apple";"Apple";"Ball";"Ball";"Car";"Car";"Dog";"Dog"}, I couldn't do that without OFFSET. I think of OFFSET like a way to perform programmatic loops. Sure, stepping from 1 to 4 with OFFSET is overkill, but when going from 1 to 10 by 2's or stepping through a range by prime numbers, or some other more complex pattern, you need OFFSET to accomplish this.

Now that I've hopefully explained why OFFSET is required, I hope our focus can turn back to the problem at hand. I'd also like to point out that I'm developing this workbook on Excel 2016 but it needs to be compatible with Excel 2010 (other users will use my template). And VBA is not an option.

Hi
See if this helps you. Try in G6 the following formula (and see with F9)

Formula:

Please Login or Register  to view this content.

I have updated the example Excel file in the link above. Please refer to this example now.

@Jose, yes, this works fine for a reference range of A1:A4, but if I try anything else (see post #3 by me above) it doesn't. Please see the new example.