Auto populate cell based on table option

**JGSmith** · 08-02-2016, 06:20 AM

Hello

I am trying to automate a few cell items based on information in another cell. Clearly I would be able to accomplish the following with very long =if() equations, but I would like to avoid that if possible.

The attachment is a picture of a small table. The left column is a city and the right column is the number of kilometers to that city.

On a separate sheet, within the same worksheet, I would like to create a drop down menu with each of the cities. Then, when a city is selected, it auto-populates another cell with the kilometer value to the right.

This will need to happen in 6 different places on the worksheet. Each city could be different in each of those places. And, cities are regularly added to this list. Therefore, I would like to create the drop down menu in such a way that I can just update the master list and all of the drop down menus will be automatically updated.

I have only been able to accomplish this will extremely long =if statements and it makes that updating process extremely inefficient.

I would be very grateful to anyone who is able to provide me with some direction on how to accomplish this.

Thank you in advance!

**Glenn Kennedy** · 08-02-2016, 06:32 AM

This is most easily done using a Named Range in Sheet 1 (CTRL-F3 to view it). It is called City. This currently allows a 100-long list of cities. Adjust the formula in the Named Range if needed. I then used DATA/VALIDATION in sheet 2 to allow the user to select only from items in the list of cities (sheet 2 column A). In column B, I used INDEX-MATCH to bring over the appropriate distance.

Formula for Named Range:
=Sheet1!$A$2:INDEX(Sheet1!$A$2:$A$100,COUNTA(Sheet1!$A$2:$A$100))

formula for INDEX-MATCH
=IFERROR(INDEX(Sheet1!B:B,MATCH(Sheet2!A2,Sheet1!$A$2:$A$100,0)),"")