Best Solution for returning data - index?

[excelnewlearner]

Hello,

Hope someone can help!

Basically what I am trying to do is return a whole row of information based on a single list item. This will allow to generate estimates and all I have to do is adjust the Quantity.

I have put a sample sheet together. So sheet one would select the item, but then populate all the information across the row as per sheet 2.

Would really appreciate any solutions.

thanks

Sample.xlsx
	A
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
A4:A24	List	=Sheet2!$A$2:$A$7

 

[excelnewlearner]

Sample.xlsx
	A	B	C	D
1
2
3	Item	QTY	DESCRIPTION	RATE
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
A4:A17	List	=Sheet2!$A$2:$A$7

 

[excelnewlearner]

Sample.xlsx
	A	B	C	D
1
2	ITEM	DESCRIPTION	RATE
3	WALLS	XXXX	5
4	PAINTING	XXXX	10
5	ROOF	XXXX	50
6	PAINTING	XXXX	10
7
8
9
10
11
Sheet2

 

[Peter_SSs]

Try this formula in cell C4 of 'Sheet1' and copy down to C17

=IF(A4="","",INDEX(Sheet2!B$2:C$17,MATCH(A4,Sheet2!A$2:A$17,0),0))

 

[excelnewlearner]

Thanks so much, that's perfect

 

[Peter_SSs]

You are welcome. Thanks for the follow-up.