SOLVED Building list using array Index and ifs from TWO different locations - odd behavior

I have been attempting to build a list with a formula i can drag down and it auto populates. The existing list it pulls from can have an arbitrary amount of space from one item to the next. I have this formula working, but when trying to pull from a second list on another sheet, it consistently is looping back to the first item on the first list. I have tried many different variations above and beyond what i have described below but can't seem to get it working. Any help would be appreciated. Here's what i have now:

Base - works fine:

{=INDEX('LIST 1'!C$3:C$300,SMALL((IF(LEN('LIST 1'!C$3:C$300),ROW(INDIRECT("1:"&ROWS('LIST 1'!C$3:C$300))))),ROW(A1)),1)}



Built on for second list on different worksheet, NOTE A9 is where this equation begins before i pull down:

{=IFERROR(INDEX('LIST 1'!J$3:J$300,SMALL((IF(LEN('LIST 1'!J$3:J$300),ROW(INDIRECT("1:"&ROWS('LIST 1'!J$3:J$300))))),ROW(A1)),1),IFERROR(INDEX('LIST 2'!$C$4:$C$300,SMALL((IF(LEN('LIST 2'!$C$4:$C$300),ROW(INDIRECT("1:"&ROWS('LIST 2'!$C$4:$C$300))))),ROW(INDIRECT("A"&ROW(A9)-COUNTA(A$1:A9)))),1),""))}

The above formula will get all the way to IFERROR(#NA,"Correct first item to pull from LIST 2") but for some reason loop back to the first item on LIST 1...even though it should (based on the remaining statement) result in "Correct first item to pull from LIST 2". The more i drag, it continually results in the first item from LIST 1



Next up I tried to shift away from IFERROR and switch to IFs:

{=IF(IFERROR(INDEX('LIST 1'!C$3:C$300,SMALL((IF(LEN('LIST 1'!C$3:C$300),ROW(INDIRECT("1:"&ROWS('LIST 1'!C$3:C$300))))),ROW(A1)),1),TRUE)=TRUE,INDIRECT(INDEX('LIST 2'!$C$4:$C$300,SMALL((IF(LEN('LIST 2'!$C$4:$C$300),ROW(INDIRECT("1:"&ROWS('LIST 2'!$C$4:$C$300))))),ROW(INDIRECT("A"&ROW(A9)-COUNTA(A$1:A9)))),1)),INDEX('LIST 1'!C$3:C$300,SMALL((IF(LEN('LIST 1'!C$3:C$300),ROW(INDIRECT("1:"&ROWS('LIST 1'!C$3:C$300))))),ROW(A1)),1))}


Similar funny business happens in the above. What ends up happening is as i drag, LIST 1 populates correctly. Once LIST 1 is no more, it does resort to attempting to pull from LIST 2 (the 'true' portion of the IF statement). It results within the IF statement as a cell reference such as 'LIST 2'!C12 (let's say this is the first item location). I added Indirect here which for some reason instead of INDIRECT('LIST 2'!C12) being a result somehow it switched and became INDIRECT("Correct first item to pull from LIST 2") which obviously doesn't work. When i remove indirect is goes back to 'LIST 2'!C12...go figure. For example:

Result A: IF(TRUE,'LIST 2'!C12,INDEX('LIST 1'!C$3:C$300,SMALL((IF(LEN('LIST 1'!C$3:C$300),ROW(INDIRECT("1:"&ROWS('LIST 1'!C$3:C$300))))),ROW(A1)),1))

or

Result B: IF(TRUE,INDIRECT("Correct first item to pull from LIST 2"),INDEX('LIST 1'!C$3:C$300,SMALL((IF(LEN('LIST 1'!C$3:C$300),ROW(INDIRECT("1:"&ROWS('LIST 1'!C$3:C$300))))),ROW(A1)),1))

Then, after, it starts calculating the 'FALSE' part of the IF statement? Only actually the LEN function portion of it (nothing else) and suddenly results in the first item from LIST 1 again!


I'm lost. I feel like i'm going in circles here lol.

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Poor you... The Forum has been fairly well behaved recently, with few glitches. The only Moderator/Admin currently online is FDibbins. You might try sending him a PM... he might know more...

I'm away for the night. I'll look back in the morning. But you'll have been fixed up by then...

Try this array formula in H9, copied down:

Formula:

Please Login or Register  to view this content.

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

To be honest... I didn't look at your monster formula. I knew that there was a better way (not using INDIRECT) and went for it.