INDEX, MATCH or VLOOKUP?

[mrroland]

Hi,

I like to know what is the best way to create a standard form in where I can select a customer by means of a drop down menu (which I can not show on the attached example) and have all data returned from a source which is usually hidden on a different sheet. The cell link of the drop down menu is E12.

Basically the values B6:B12 will match the values F5:L5. In this example customer 2 is selected. I would like that in the range c6:c12 the values are returned of F7:L7

See attached example.

Example.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	111	111	111	111	111	111	111	111	111	111	111	111	111
2	111	111	111	111	111	111	111	111	111	111	111	111	111
3	111	111	111	111	111	111	111	111	111	111	111	111	111
4	111	111	111	111	Source (normally on a different sheet)	111	111	111	111	111	111	111	111
5	111	111	Result of customer 2	111	Site	Building	Address	Place	Post Code	Country	Phone	Fax	111
6	111	Building	zzz	111	Customer 1	aaa	bbb	ccc	ddd	eee	fff	ggg	111
7	111	Address	yyy	111	Customer 2	zzz	yyy	xxx	www	vvv	uuu	ttt	111
8	111	Place	xxx	111	Customer 3	abc	def	ghi	jkl	mno	pqr	stu	111
9	111	Post Code	www	111	Customer 4	qwe	rty	uio	plk	jhg	fds	saz	111
10	111	Country	vvv	111	Customer 5	edc	wsx	qaz	vbn	mkj	hyg	zxc	111
11	111	Phone	uuu	111	111	111	111	111	111	111	111	111	111
12	111	Fax	ttt	111	2	111	111	111	111	111	111	111	111
13	111	111	111	111	111	111	111	111	111	111	111	111	111
14	111	111	111	111	111	111	111	111	111	111	111	111	111
15	111	111	111	111	111	111	111	111	111	111	111	111	111
Example

I hope the example is clear. I will be happy to give any of you more information
Your help is greatly appreciated.

Kind regards,

Roland

 

[DonkeyOte]

you can do both a HLOOKUP and VLOOKUP in one formula (or match etc)

eg

=VLOOKUP(customer cell ref,data range, HLOOKUP(criteria - cell ref),data range,row number of titles,0),0)

 

[Aladin Akyurek]

In C6 enter & copy down:

=VLOOKUP(C5,$E$5:$L$10,MATCH(B6,$E$5:$L$5,0),0)

 

[mrroland]

Alladin,

Your formual worked when i changed it to =VLOOKUP($C$5,$E$5:$L$10,MATCH(B6,$E$5:$L$5,0),0) but what I would like is to make this go automatically with a pull down menu (could not get this on the example) For example if I select customer 1 the data of this customer is returned in range C6:C12, but if I select cutomer 3 I want the data of customer 3 to be returned in range C6:C12

Many thanks for helping me so far.

Roland

 

[DonkeyOte]

Aladin's formula is working off the assumption that C5 is the linked cell to the combo box value - ie 1,2,3 etc...

 

[mrroland]

Oeps,

Sorry, I got it fixed now. Many thanks for your help.

Kind regards,
Roland

 

[Northerner]

Aladin,
I have used the same process with our Customer DB containing over 2000 entries. As the coloum headings are fixed wouldn't it be quicker (on bigger arrays) to change the ,MATCH(B6,$E$5:$L$5,0), in each row of the display area with the col_index_num. eg.Building =2, Address =3?

 

[Aladin Akyurek]

Aladin,
I have used the same process with our Customer DB containing over 2000 entries. As the coloum headings are fixed wouldn't it be quicker (on bigger arrays) to change the ,MATCH(B6,$E$5:$L$5,0), in each row of the display area with the col_index_num. eg.Building =2, Address =3?

Yes, definitely.