从多列中提取Excel的下拉列表

Question

我一直试图弄清楚这两个小时，现在我只需要寻求帮助。我想在Excel中有一个下拉列表，当你选择一个项目时，它也会从它旁边的列中获取信息。

表1

Name | City | State 
John | Cty1 | CA 
Luke | Cty2 | IA 
Kris | Cty3 | TX 

SHEET 2

Name | City | State 
___v | ____ | __ 

所以我试图做的是，当你选择在表2中的名字，它会采取城市和国家从表1和自动将其填充到SHEET 2的行中。有人可以帮我解决这个问题吗？我疯了。

谢谢。

Answer 1

试试这个： -

To create dropdown for Name 

1. First copy paste values of NAme from Sheet1 in Sheet2. Say you pasted them in A1 to A3. 
2. Under name (say D3 cell in sheet2) column go to D4 cell just below name,then Go the 'Data validation' tab under 'data' tab. Select 'data validation' again, a dialog box will appear. 
3. In the dialog box under 'Allow' select 'List' and then in 'Source' make a selection from A1 to A3. 


    Now Use this formula in sheet2 
    for city 
    =IFERROR(VLOOKUP(D4,Sheet1!$D$15:$F$18,2,0)," ") 

    for state 
    =IFERROR(VLOOKUP(D4,Sheet1!$D$15:$F$18,3,0)," ") 

    This is assuming in Sheet1 D15 to D18 has values for column Name & D4 is value of Name or Name dropdown in Sheet2 

希望这有助于:-)