Populating an array from a list

New Contributor

The names on the far left are meant to be fixed in the first column. The second column has a drop down list of states, and I want an array to populate based off the selected state. The idea is that the data that populates in the second column when selecting the state will match up with the names on the first column. Please see photos for reference.


Annotation 2021-10-12 163232.pngAnnotation 2021-10-12 163232b.png



I created the list of states using a named range, and the array data is populating from a table in a different tab. 

This is the formula I currently have in place but it only works for Alabama, however I need it to be able to populate the corresponding array for the state selected from the list. 

=IF(B3=Data!B3, Alabama, "Empty")


I am using the latest version of Excel on a Windows 10 desktop. 


2 Replies
best response confirmed by allyreckerman (Microsoft)


Perhaps something like




The defined names for multi-word states such as New Mexico should contain an underscore _ instead of a space.

Thank you!!! This does exactly what I need it to do! Idk why it wont let me log back in to the username I used but I am @elopezpcg and can confirm this worked for me.