Lookpup value

[ziad alsayed]

dear all

i am trying to lookup value from one sheet to another base on two condition (Year and Item). below is the data i need to put the price in first table based on the data in the second table

table 1

Excel Workbook
	A	B	C	D	E
1	Branch	Year	Item	Qty	Unit Price
2	PHC	2008	Big Diary	800
3	PHC	2009	Big Diary	800
4	PHC	2010	Big Diary	700
5	PHC	2011	Big Diary	650
6	PHC	2012	Big Diary	500
7	PHC	2008	Small Diary	500
8	PHC	2009	Small Diary	500
9	PHC	2010	Small Diary	400
10	PHC	2011	Small Diary	300
11	PHC	2012	Small Diary	0
12	PHC	2008	Table Pad	700
13	PHC	2009	Table Pad	700
14	PHC	2010	Table Pad	600
Data

Excel 2010


table 2

Excel Workbook
	A	B	C	D	E	F
1		2008	2009	2010	2011	2012
2	Dollar rate	150	150	150	150	150
3	Big Diary	$4.50	$4.50	$5.00	$5.50	$5.50
4	Small Diary	$1.50	$1.50	$1.50	$1.80	$1.80
5	Table Pad	$1.20	$1.20	$1.20	$1.40	$1.40
6	Table Calender	$1.00	$1.00	$1.00	$1.10	$1.10
7	Greeting Cards	$0.50	$0.50	$0.50	$0.50	$0.50
8	Wine Sets	$35.00	$40.00	$42.00	$45.00	$45.00
Prices

Excel 2010


hope you can assist.

 

[Aladin Akyurek]

dear all

i am trying to lookup value from one sheet to another base on two condition (Year and Item). below is the data i need to put the price in first table based on the data in the second table

table 1

Excel Workbook
	A	B	C	D	E
1	Branch	Year	Item	Qty	Unit Price
2	PHC	2008	Big Diary	800
3	PHC	2009	Big Diary	800
4	PHC	2010	Big Diary	700
5	PHC	2011	Big Diary	650
6	PHC	2012	Big Diary	500
7	PHC	2008	Small Diary	500
8	PHC	2009	Small Diary	500
9	PHC	2010	Small Diary	400
10	PHC	2011	Small Diary	300
11	PHC	2012	Small Diary	0
12	PHC	2008	Table Pad	700
13	PHC	2009	Table Pad	700
14	PHC	2010	Table Pad	600
Data

Excel 2010


table 2

Excel Workbook
	A	B	C	D	E	F
1		2008	2009	2010	2011	2012
2	Dollar rate	150	150	150	150	150
3	Big Diary	$4.50	$4.50	$5.00	$5.50	$5.50
4	Small Diary	$1.50	$1.50	$1.50	$1.80	$1.80
5	Table Pad	$1.20	$1.20	$1.20	$1.40	$1.40
6	Table Calender	$1.00	$1.00	$1.00	$1.10	$1.10
7	Greeting Cards	$0.50	$0.50	$0.50	$0.50	$0.50
8	Wine Sets	$35.00	$40.00	$42.00	$45.00	$45.00
Prices

Excel 2010


hope you can assist.

Sheet1, E2, copy down:

=INDEX(Sheet2!$B$3:$F$8,MATCH(C2,Sheet2!$A$2:$A$8,0),MATCH(B2,Sheet2!$B$1:$F$1,0))

 

[ziad alsayed]

thanks aladin ,this is perfect,please can you explain it.

 

[Aladin Akyurek]

thanks aladin ,this is perfect,...

You are welcome. Thanks for providing feedback.

...please can you explain it.

INDEX, in order to return a value from the result range it's given, needs the vertical position and the horizontal position of that value.

In the suggested formula:

=INDEX(Sheet2!$B$3:$F$8,MATCH(C2,Sheet2!$A$2:$A$8,0),MATCH(B2,Sheet2!$B$1:$F$1,0))

Sheet2!$B$3:$F$8 is the result range;

MATCH(C2,Sheet2!$A$2:$A$8,0) calculates the vertical position; and

MATCH(B2,Sheet2!$B$1:$F$1,0) calculates the horizontal position.

The cell in Sheet2!$B$3:$F$8 is where the two positions meet/intersect

 

[ziad alsayed]

thank you sir, this is clear.

 

[Aladin Akyurek]

thank you sir, this is clear.

You are welcome.