Ppulating cells where adjacent cells are empty

[bearcub]

I have a spreadsheet where I am trying to populate positions from a lookup table on another sheet (LocalStateSCC Falcon positions) where all the source positions are in a single column.

I have a column for Local, a column for State and a column for SCC in my destination sheet (the one which has the [inc_cst_recno field per the formula below).

Some members might have 2 or more records because they hold different positions.

For example, if a member holds 2 Local positions, 1 State Council position and 1 Service Center Council position then I will need to populate 4 rows of data, 2 for local, 1 for state and 1 for SCC on the destination sheet.

If I use a vlookup by itself, the first row in my destination sheet will contain 1 local, 1 state and 1 SCC position - which isn't what I need.

State position should be on row 3 (the 3rd record) and SCC position on row 4 (the 4th record) so there isn't a position overlap - I have 1 record (row) per position.

This is the formula I'm using to populate the Local positions (it seems that many members how multiple local positions too):

[B]=IFERROR(INDEX('LocalStateSCC Falcon Positions'!$C$3:$C$553,AGGREGATE(15,3,([@[ind_cst_recno]]='LocalStateSCC Falcon Positions'!$A$3:$A$553)*(ROW('LocalStateSCC Falcon Positions'!$A$3:$A$553)-ROW('LocalStateSCC Falcon Positions'!$A$2))/([@[ind_cst_recno]]='LocalStateSCC Falcon Positions'!$A$3:$A$553),COUNTIF($A$2:A2,[@[ind_cst_recno]]))),"")[/B]
[code]/

This formula will extract duplicate ID numbers but populate include the 2nd, 3rd, etc positions. 

I was thinking of using is this formula in the State and SCC columns as well and link them to the appropriate lookup table (the structure is the same as the local table). 

But, I would add an if statement in front of this formula  to check to see if the local column was blank the formula ran (the state fornula would look for blank cells in the local field and the SCC formula would have to look at both the Local and State adjacent cells to see if they were blank). I would have to do this same sort of thing in the SCC column as well.

Do you think this type of approach might work? Or, could you provide me with a better one?

Thank you for your help,

Michael