Formula returning data matching two criteria from a table

[lechev65]

Hi, </SPAN>
I have the below data in “Sheet2”</SPAN>

	A</SPAN></SPAN>	B</SPAN></SPAN>	C</SPAN></SPAN>	D</SPAN></SPAN>	E</SPAN></SPAN>	F</SPAN></SPAN>
1</SPAN></SPAN>	ITEM</SPAN></SPAN>	2008</SPAN></SPAN>	2009</SPAN></SPAN>	2010</SPAN></SPAN>	2011</SPAN></SPAN>	2012</SPAN></SPAN>
2</SPAN></SPAN>	bolt</SPAN></SPAN>	9-Aug-08</SPAN></SPAN>	6-Jun-09</SPAN></SPAN>	10-Oct-10</SPAN></SPAN>	6-Jan-11</SPAN></SPAN>	7-Jul-12</SPAN></SPAN>
3</SPAN></SPAN>	nut</SPAN></SPAN>	6-Jun-08</SPAN></SPAN>	2-Feb-09</SPAN></SPAN>	9-Sep-10</SPAN></SPAN>	1-Aug-11</SPAN></SPAN>	9-Sep-12</SPAN></SPAN>
4</SPAN></SPAN>	splint</SPAN></SPAN>	7-Mar-08</SPAN></SPAN>	1-Jan-09 </SPAN></SPAN>	8-Aug-10</SPAN></SPAN>	9-Sep-11</SPAN></SPAN>	9-Aug-12</SPAN></SPAN>
5</SPAN></SPAN>	frame</SPAN></SPAN>	2-Jan-08 </SPAN></SPAN>	7-Jul-09</SPAN></SPAN>	2-Jan-10</SPAN></SPAN>	7-Jul-11</SPAN></SPAN>	6-Jun-12</SPAN></SPAN>
6 </SPAN></SPAN>	bar</SPAN></SPAN>	3-Mar-08</SPAN></SPAN>	9-Sep-09</SPAN></SPAN>	3-Mar-10</SPAN></SPAN>	12-Dec-11</SPAN></SPAN>	10-Oct-12</SPAN></SPAN>
7</SPAN></SPAN>	gear</SPAN></SPAN>	5-May-08</SPAN></SPAN>	4-Apr-09</SPAN></SPAN>	6-Jun-10</SPAN></SPAN>	7-Sep-11</SPAN></SPAN>	9-Sep-12</SPAN></SPAN>

<TBODY>
</TBODY>

I need to get data in “Sheet1” matching item and year criteria.</SPAN>

	A</SPAN></SPAN>	B</SPAN></SPAN>
1</SPAN></SPAN>	ITEM</SPAN></SPAN>	2009</SPAN></SPAN>
2</SPAN></SPAN>	nut</SPAN></SPAN>	2-Feb-09</SPAN></SPAN>
3</SPAN></SPAN>	frame</SPAN></SPAN>	7-Jul-09</SPAN></SPAN>
4</SPAN></SPAN>	bar</SPAN></SPAN>	9-Sep-09</SPAN></SPAN>

<TBODY>
</TBODY>

Thank you in advance.</SPAN>
Krasimir</SPAN>

 

[jim may]

Copy down Cell B2 Formula

Excel Workbook
	A	B
1	ITEM	2009
2	nut	2-Feb-09
3	frame	7-Jul-09
4	bar	9-Sep-09
Sheet1

Excel 2007

Cell Formulas
Range	Formula
B2	=INDEX(Sheet2!$B$2:$F$7,MATCH(Sheet1!$A2,Sheet2!$A$2:$A$7,0),MATCH(Sheet1!B$1,Sheet2!$B$1:$F$1,0))

 

[lechev65]

Thank you!

 

[jim may]

What Index does..

Returns the coorinates of a matrix..

=Index(SourceDataTable,Row#Desired,Column#Desired)

Glad it helped you..

Jim