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

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

<tbody>
</tbody>

 

[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?