Drop Down List with Linked Columns

[bengal1022]

Hello:

I am trying to figure out how to create a drop down list with multiple linked columns; but let me explain as it is different from what I am seeing on the forum and other sites. 

I have a worksheet in the data base with vendor contact information. It has 4 columns:

Vendor Name

Contact Name

Email

Phone Number

I have another worksheet with Purchase Information, with the same four columns. I want to know how I can create a drop down list in this worksheet that will allow one to select the "Vendor name" from a drop down list, and once it is selected then the other 3 columns would populate with the respective information from the vendor contact worksheet. 

I have attached the spreadsheet as reference.

Is this possible? And if so, how would I go about it?

Thanks

[#2 · Accepted solution]

@bengal1022 

I'd create two named ranges

one for data validation list

='Vendor Contacts'!$A$2:INDEX('Vendor Contacts'!$A:$A,COUNTA('Vendor Contacts'!$A:$A))

and another for vendor info range

='Vendor Contacts'!$B$2:INDEX('Vendor Contacts'!$D:$D,COUNTA('Vendor Contacts'!$A:$A))

using first in data validation and second to pickup the info as

=IFNA(INDEX(VendorsRange,MATCH($L2,VendorsList,0),MATCH(M$1,'Vendor Contacts'!$B$1:$D$1,0)),"")

[#3]

Thanks Sergei. Works like a charm. You're a lifesaver.

Best

[#4]

@bengal1022 

You are welcome

[#5]

Select the cell where you want the first (main) drop down list.
Go to Data –> Data Validation. ...
In the data validation dialog box, within the settings tab, select List.
In Source field, specify the range that contains the items that are to be shown in the first drop down list.
Click OK.