Returning Multiple results to then create a drop down list

[Davereau]

We utilize the following table in a database for customer contact information. I am creating a new form in excel that requires an individual contact from a customer. Since we have multiple contacts for each customer, i don't think a VLOOKUP function would work. On a separate tab, I am trying to create a list of the contact names for just the customer number I would input (column B) in the new excel form I am creating utilizing another excel file called table_CM_Contact.xlsx. After that, i can utilize VLOOKUP for all the other information. I have been researching and there is an index formula I have tried but it returns results for the whole table, not just the Cust Nbr that I am searching.

Any ideas?

Thanks in advance
David

table_CM_Contact.xlsx
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ID[/TD]
[TD]Cust Nbr[/TD]
[TD]Contact[/TD]
[TD]Title[/TD]
[TD]Salutation[/TD]
[TD]Department[/TD]
[TD]Phone[/TD]
[TD]Extension[/TD]
[TD]Email[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1
[/TD]
[TD]0100001[/TD]
[TD]Bret Normal[/TD]
[TD]VP[/TD]
[TD][/TD]
[TD][/TD]
[TD]123-456-7890[/TD]
[TD]110[/TD]
[TD]bretn@xyz.com[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]0100001[/TD]
[TD]David Great[/TD]
[TD]Inside[/TD]
[TD][/TD]
[TD][/TD]
[TD]123-456-7890[/TD]
[TD]111[/TD]
[TD]davidg@xyz.com[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]3[/TD]
[TD]2000009[/TD]
[TD]Matt Goof[/TD]
[TD]Buyer[/TD]
[TD][/TD]
[TD][/TD]
[TD]456-789-0123[/TD]
[TD][/TD]
[TD]mattg@goof.com[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]4[/TD]
[TD]2000009[/TD]
[TD]Janet Right[/TD]
[TD]Buyer[/TD]
[TD][/TD]
[TD][/TD]
[TD]456-789-0124[/TD]
[TD][/TD]
[TD]janetr@goof.com[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]5[/TD]
[TD]2000009[/TD]
[TD]Jeff Mighty[/TD]
[TD]Engineer[/TD]
[TD][/TD]
[TD][/TD]
[TD]456-789-0125[/TD]
[TD][/TD]
[TD]jeffm@goof.com[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]6[/TD]
[TD]0300040[/TD]
[TD]Maria Clear[/TD]
[TD]Buyer[/TD]
[TD][/TD]
[TD][/TD]
[TD]789-012-3456[/TD]
[TD][/TD]
[TD]mariac@help.com[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]7[/TD]
[TD]0300040[/TD]
[TD]Pat Murphy[/TD]
[TD]Buyer[/TD]
[TD][/TD]
[TD][/TD]
[TD]789-012-3456[/TD]
[TD][/TD]
[TD]patm@help.com[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]8[/TD]
[TD]0300040[/TD]
[TD]Dillon Man[/TD]
[TD]Sales[/TD]
[TD][/TD]
[TD][/TD]
[TD]789-012-3456[/TD]
[TD][/TD]
[TD]dillonm@help.com[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]9[/TD]
[TD]0400050[/TD]
[TD]Adriana
Serve[/TD]
[TD]Buyer[/TD]
[TD][/TD]
[TD][/TD]
[TD]012-345-6789[/TD]
[TD][/TD]
[TD]adrianas@don.com[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]10[/TD]
[TD]0400050[/TD]
[TD]Francis Key[/TD]
[TD]Buyer[/TD]
[TD][/TD]
[TD][/TD]
[TD]013-345-6789[/TD]
[TD][/TD]
[TD]francisk@don.com[/TD]
[/TR]
</tbody>[/TABLE]

 

[peterhinton]

if you had on a sheet the customer name in say A1, then contact number in B1, and a second contact number in B2,

The second Customer name in A3, and contact number in B3 etc.

You could use an offset and match formula to get the numbers and use this in the data validation to create a drop down list of the number etc.

 

[peterhinton]

use list in data validation and use =OFFSET(INDIRECT(("A"&MATCH(J4,A:A,0))),0,2,VLOOKUP(J4,A:B,2,FALSE),1)

name of contact in A1, number of different contact numbers in B1, Contact numbers in C1,C2,C3

Then leave a blank,

Contact 2 goes in A5, and repeat etc.

 

[Davereau]

Is that utilizing the customer number as the data to match?