Excel 2007 : Strange IFERROR and OFFSET behaviour

[ups]

Hello everybody, I wonder if you can help me!

So this is the data I want to parse.

Please Login or Register  to view this content.

I want to make it like this:

Please Login or Register  to view this content.

I'm using this code for Us1

Please Login or Register  to view this content.

which is then Ctrl+Shift+enter into the several cells in front of Us users.

Where 'Main' has the first table, C3 has the offset where Us1 is first mentioned and B3 has a count of how many items Us1 has.

But there are 2 problems:
This is what I'm getting!

Please Login or Register  to view this content.

1) IFERROR does not seem to be working as some #N/A are displayed
2) If the Counter in B3 is 1, then the value is repeated where it should only show 1 value (see the case of Us3).

I've added a excel file that replicates the issue in case if this is not enough information (or maybe I'm just not that good explaining myself)!

[squiggler47]

Problem is if you evaluate, your offset is not returning any errors!

So in the case of US2 it returns 2 items with no errors, so you are then have no items to put in the last 3 rows!

use evaluate formula on the cell with the error!

[DonkeyOte]

There's no need for an array here - nor any need for Volatile function like OFFSET... you can use INDEX... ie

F2: =MATCH($D2,$A:$A,0)
copy down to F5

H2: =IF(COLUMNS($H2:H2)>$E2,"",INDEX($B:$B,$F2+(COLUMNS($H2:H2)-1)))
apply across matrix (H2:K5)

[ups]

There's no need for an array here - nor any need for Volatile function like OFFSET... you can use INDEX... ie

F2: =MATCH($D2,$A:$A,0)
copy down to F5

H2: =IF(COLUMNS($H2:H2)>$E2,"",INDEX($B:$B,$F2+(COLUMNS($H2:H2)-1)))
apply across matrix (H2:K5)

Thanks! Works Perfectly!