Drop Down List created from 2 columns of info

[Simon.xlsx]

Hi All,

I have a list of names in column B, I can easily put this into a list

I also have a different list of names in Column C

These two list are feeding from sharepoint and are updated regularly

What I want to do is create a drop down list that will list all the names in Column B and Column C, while keeping the initial lists in there separate columns

I have been trying to find a formula to lookup both columns and return all the names into another column (E) using the formula below

=IFERROR(INDEX($B$2:$C$600,MOD(ROWS(E$2:E2)-1,ROWS($B$2:$C$600))+1,INT((ROWS(E$2:E2)-1)/ROWS($B$2:$C$600))+1),"")

the plan been to just put column E into my drop down list, but this formula is returning alot of blanks between Column B + C in the list (there might be 100 names in column B and only 30 in column C)

To try remove the blanks I tried another formula pulling the info into Column F,

=IFERROR(INDEX($E$2:$E$5000,AGGREGATE(15,6,(ROW($E$2:$E$5000)-ROW($E$2)+1)/($E$2:$E$5000<>""),ROWS(F$2:F2))),"")

But while this did remove the blanks (about 500 blanks) it left me with about 70 "0" errors.

My poor head is melted - any help be great

can provide a sample if needed

[hemesh]

in column C2 copy paste below then hold control and shift together and hit enter to make it array formula
=IFERROR(IF(ROW(A1)>SUM(IF($B$1:$B$500<>"",1)),INDEX($C$1:$C$500,SMALL(IF($C$1:$C$500<>"",ROW($C$1:$C$500)),ROW(A1)-SUM(IF($B$1:$B$500<>"",1)))),INDEX($B$1:$B$500,SMALL(IF($B$1:$B$500<>"",ROW($B$1:$B$500)),ROW(A1)))),"") then drag down change the references according to your original data

[Simon.xlsx]

Thanks for your quick response

I had since found a formula that seems to work

=IFERROR(INDEX(List1, ROWS(E2:$E$2)), IFERROR(INDEX(List2, ROWS(E2:$E$2)-ROWS(List1)), ""))

Giving the two columns named ranges list1 and List2 and returning the info to column E

This might not workout in the long run so i will try your formula aswell an see what fits best

Thanks again,

Simon

[hemesh]

Thanks for the feedback Simon, but I think you can make it

=IF(ROW(A1)<ROWS(list1),INDEX(list1, ROWS(E$2:$E2)), IFERROR(INDEX(list2, ROWS(E$2:$E2)-ROWS(list1)), ""))

but will also take into account if there are blanks
if there are no blanks then its good to go