# Formula returning data matching two criteria from a table

#### lechev65

##### Board Regular
Hi, </SPAN>
I have the below data in “Sheet2”</SPAN>
 A B C D E F 1 ITEM 2008 2009 2010 2011 2012 2 bolt 9-Aug-08 6-Jun-09 10-Oct-10 6-Jan-11 7-Jul-12 3 nut 6-Jun-08 2-Feb-09 9-Sep-10 1-Aug-11 9-Sep-12 4 splint 7-Mar-08 1-Jan-09 8-Aug-10 9-Sep-11 9-Aug-12 5 frame 2-Jan-08 7-Jul-09 2-Jan-10 7-Jul-11 6-Jun-12 6 bar 3-Mar-08 9-Sep-09 3-Mar-10 12-Dec-11 10-Oct-12 7 gear 5-May-08 4-Apr-09 6-Jun-10 7-Sep-11 9-Sep-12

<TBODY>
</TBODY>

I need to get data in “Sheet1” matching item and year criteria.</SPAN>
 A B 1 ITEM 2009 2 nut 2-Feb-09 3 frame 7-Jul-09 4 bar 9-Sep-09

<TBODY>
</TBODY>

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

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Copy down Cell B2 Formula
Excel Workbook
AB
1ITEM2009
2nut2-Feb-09
3frame7-Jul-09
4bar9-Sep-09
Sheet1
Excel 2007
Cell Formulas
RangeFormula
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))

Thank you!

What Index does..

Returns the coorinates of a matrix..

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

Glad it helped you..

Jim

Replies
0
Views
791
Replies
0
Views
189
Replies
1
Views
146
Replies
5
Views
158
Replies
3
Views
86

1,203,094
Messages
6,053,507
Members
444,667
Latest member
KWR21

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back