# Create Drop-down List from Multiple Columns

#### thotto

##### New Member
I have different accounts listed horizontally as in the picture. I'd like to create a data validation in a new cell that containing all the accounts I have i.e. "My credit card" and "Paypal". I've been working at this problem for hours. INDEX and MATCH don't work in this case. Why real-world data can't be as tame as what I was taught in Excel courses ? Can anyone offer any help?

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

#### davesexcel

##### Well-known Member
Are you asking how to create a list to place in the data validation list?

#### jasonb75

##### Well-known Member
Assuming that your account names span the range A3:Z3, enter this formula into AA2, then fill down to create a list of account names in consecutive cells.

=IFERROR(INDEX(\$A\$3:\$Z\$3,AGGREGATE(15,6,COLUMN(\$A\$3:\$Z\$3)/ISTEXT(\$A\$3:\$Z\$3),ROWS(\$AA\$2:\$AA2))),"")

Then for your validation list, use

=OFFSET(\$AA\$2,0,0,COUNTIF(\$AA:\$AA,"<>"),1)

The list doesn't need to start in AA2, just somewhere convenient. It could be in a different sheet if needed.

#### thotto

##### New Member
Assuming that your account names span the range A3:Z3, enter this formula into AA2, then fill down to create a list of account names in consecutive cells.

=IFERROR(INDEX(\$A\$3:\$Z\$3,AGGREGATE(15,6,COLUMN(\$A\$3:\$Z\$3)/ISTEXT(\$A\$3:\$Z\$3),ROWS(\$AA\$2:\$AA2))),"")

Then for your validation list, use

=OFFSET(\$AA\$2,0,0,COUNTIF(\$AA:\$AA,"<>"),1)

The list doesn't need to start in AA2, just somewhere convenient. It could be in a different sheet if needed.
Thank you for taking the time to help me. I've learned several formulas that I'm not aware of, but I guess I'd need to reorganize the data.

#### jasonb75

##### Well-known Member
Does the method suggested not work for you?

While well organised data is always advisable, re-organising if you don't need to could solve one problem and create another.