VLOOKUP/MATCH formula needed

[Craig4670]

Hello!

I have a question regarding the VLOOKUP/MATCH function. I am trying to figure out how to get the year to year data per month. I have data for 2010 & 2011. I am able to pull the information by month but not by year.

On the original spread sheet, there is a combo box that the user can choose information by month to month or year to year. The month to month is working properly. When the month is selected ex(Jan-10), the Total for 2010 should also be pulled.

Can someone please help? Below is a sample of the data.

Thanks in advance for your help





<table border="0" cellpadding="0" cellspacing="0" width="1587"><col style="width: 48pt;" width="64"> <col style="width: 41pt;" width="54"> <col style="width: 42pt;" width="56"> <col style="width: 43pt;" width="57"> <col style="width: 41pt;" width="54"> <col style="width: 44pt;" width="58"> <col style="width: 41pt;" width="54"> <col style="width: 40pt;" width="53"> <col style="width: 43pt;" width="57"> <col style="width: 42pt;" width="56"> <col style="width: 41pt;" width="54"> <col style="width: 44pt;" width="58"> <col style="width: 43pt;" width="57"> <col style="width: 74pt;" width="98"> <col style="width: 40pt;" width="53" span="2"> <col style="width: 41pt;" width="54"> <col style="width: 40pt;" width="53"> <col style="width: 42pt;" width="56"> <col style="width: 40pt;" width="53" span="2"> <col style="width: 41pt;" width="54"> <col style="width: 40pt;" width="53" span="2"> <col style="width: 42pt;" width="56"> <col style="width: 41pt;" width="54"> <col style="width: 84pt;" width="112"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl28" style="height: 12.75pt; width: 48pt;" width="64" height="17">Supplier</td> <td class="xl29" style="border-left: medium none; width: 41pt;" width="54">Jan-10</td> <td class="xl29" style="border-left: medium none; width: 42pt;" width="56">Feb-10</td> <td class="xl29" style="border-left: medium none; width: 43pt;" width="57">Mar-10</td> <td class="xl29" style="border-left: medium none; width: 41pt;" width="54">Apr-10</td> <td class="xl29" style="border-left: medium none; width: 44pt;" width="58">May-10</td> <td class="xl29" style="border-left: medium none; width: 41pt;" width="54">Jun-10</td> <td class="xl29" style="border-left: medium none; width: 40pt;" width="53">Jul-10</td> <td class="xl29" style="border-left: medium none; width: 43pt;" width="57">Aug-10</td> <td class="xl29" style="border-left: medium none; width: 42pt;" width="56">Sep-10</td> <td class="xl29" style="border-left: medium none; width: 41pt;" width="54">Oct-10</td> <td class="xl29" style="border-left: medium none; width: 44pt;" width="58">Nov-10</td> <td class="xl29" style="border-left: medium none; width: 43pt;" width="57">Dec-10</td> <td class="xl30" style="border-left: medium none; width: 74pt;" width="98">Total For 2010</td> <td class="xl29" style="border-left: medium none; width: 40pt;" width="53">Jan-11</td> <td class="xl29" style="border-left: medium none; width: 40pt;" width="53">Feb-11</td> <td class="xl29" style="border-left: medium none; width: 41pt;" width="54">Mar-11</td> <td class="xl29" style="border-left: medium none; width: 40pt;" width="53">Apr-11</td> <td class="xl29" style="border-left: medium none; width: 42pt;" width="56">May-11</td> <td class="xl29" style="border-left: medium none; width: 40pt;" width="53">Jun-11</td> <td class="xl29" style="border-left: medium none; width: 40pt;" width="53">Jul-11</td> <td class="xl29" style="border-left: medium none; width: 41pt;" width="54">Aug-11</td> <td class="xl29" style="border-left: medium none; width: 40pt;" width="53">Sep-11</td> <td class="xl29" style="border-left: medium none; width: 40pt;" width="53">Oct-11</td> <td class="xl29" style="border-left: medium none; width: 42pt;" width="56">Nov-11</td> <td class="xl29" style="border-left: medium none; width: 41pt;" width="54">Dec-11</td> <td class="xl30" style="border-left: medium none; width: 84pt;" width="112">Total For 2011 </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt;" height="17"> </td> <td class="xl27" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl27" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl27" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl27" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl27" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl27" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl27" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl27" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl27" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl27" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl27" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl27" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl25" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">Test 1 </td> <td class="xl27" style="border-top: medium none; border-left: medium none;">$100.00</td> <td class="xl27" style="border-top: medium none; border-left: medium none;">$120.00</td> <td class="xl27" style="border-top: medium none; border-left: medium none;">$140.00</td> <td class="xl27" style="border-top: medium none; border-left: medium none;">$160.00</td> <td class="xl27" style="border-top: medium none; border-left: medium none;">$180.00</td> <td class="xl27" style="border-top: medium none; border-left: medium none;">$200.00</td> <td class="xl27" style="border-top: medium none; border-left: medium none;">$220.00</td> <td class="xl27" style="border-top: medium none; border-left: medium none;">$240.00</td> <td class="xl27" style="border-top: medium none; border-left: medium none;">$260.00</td> <td class="xl27" style="border-top: medium none; border-left: medium none;">$280.00</td> <td class="xl27" style="border-top: medium none; border-left: medium none;">$300.00</td> <td class="xl27" style="border-top: medium none; border-left: medium none;">$320.00</td> <td class="xl27" style="border-top: medium none; border-left: medium none;">$2,520.00</td> <td class="xl31" style="border-top: medium none; border-left: medium none;" align="right">$400.00</td> <td class="xl31" style="border-top: medium none; border-left: medium none;" align="right">$420.00</td> <td class="xl31" style="border-top: medium none; border-left: medium none;" align="right">$440.00</td> <td class="xl31" style="border-top: medium none; border-left: medium none;" align="right">$460.00</td> <td class="xl31" style="border-top: medium none; border-left: medium none;" align="right">$480.00</td> <td class="xl31" style="border-top: medium none; border-left: medium none;" align="right">$500.00</td> <td class="xl31" style="border-top: medium none; border-left: medium none;" align="right">$520.00</td> <td class="xl31" style="border-top: medium none; border-left: medium none;" align="right">$540.00</td> <td class="xl31" style="border-top: medium none; border-left: medium none;" align="right">$560.00</td> <td class="xl31" style="border-top: medium none; border-left: medium none;" align="right">$580.00</td> <td class="xl31" style="border-top: medium none; border-left: medium none;" align="right">$600.00</td> <td class="xl31" style="border-top: medium none; border-left: medium none;" align="right">$620.00</td> <td class="xl27" style="border-top: medium none; border-left: medium none;">$6,120.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">Test 2</td> <td class="xl27" style="border-top: medium none; border-left: medium none;">$110.00</td> <td class="xl27" style="border-top: medium none; border-left: medium none;">$130.00</td> <td class="xl27" style="border-top: medium none; border-left: medium none;">$150.00</td> <td class="xl27" style="border-top: medium none; border-left: medium none;">$170.00</td> <td class="xl27" style="border-top: medium none; border-left: medium none;">$190.00</td> <td class="xl27" style="border-top: medium none; border-left: medium none;">$210.00</td> <td class="xl27" style="border-top: medium none; border-left: medium none;">$230.00</td> <td class="xl27" style="border-top: medium none; border-left: medium none;">$250.00</td> <td class="xl27" style="border-top: medium none; border-left: medium none;">$270.00</td> <td class="xl27" style="border-top: medium none; border-left: medium none;">$290.00</td> <td class="xl27" style="border-top: medium none; border-left: medium none;">$310.00</td> <td class="xl27" style="border-top: medium none; border-left: medium none;">$330.00</td> <td class="xl27" style="border-top: medium none; border-left: medium none;">$2,640.00</td> <td class="xl31" style="border-top: medium none; border-left: medium none;" align="right">$410.00</td> <td class="xl31" style="border-top: medium none; border-left: medium none;" align="right">$430.00</td> <td class="xl31" style="border-top: medium none; border-left: medium none;" align="right">$450.00</td> <td class="xl31" style="border-top: medium none; border-left: medium none;" align="right">$470.00</td> <td class="xl31" style="border-top: medium none; border-left: medium none;" align="right">$490.00</td> <td class="xl31" style="border-top: medium none; border-left: medium none;" align="right">$510.00</td> <td class="xl31" style="border-top: medium none; border-left: medium none;" align="right">$530.00</td> <td class="xl31" style="border-top: medium none; border-left: medium none;" align="right">$550.00</td> <td class="xl31" style="border-top: medium none; border-left: medium none;" align="right">$570.00</td> <td class="xl31" style="border-top: medium none; border-left: medium none;" align="right">$590.00</td> <td class="xl31" style="border-top: medium none; border-left: medium none;" align="right">$610.00</td> <td class="xl31" style="border-top: medium none; border-left: medium none;" align="right">$630.00</td> <td class="xl27" style="border-top: medium none; border-left: medium none;">$6,240.00</td> </tr> </tbody></table>


Feb-10 is selected for supplier Test 1

$120.00 should be displayed
$2,520.00 should be displayed as well

 

[AlphaFrog]

Excel Workbook
	A	B	C	D	E
1	Supplier	10-Jan	10-Feb	10-Mar	10-Apr
2	Test 1	$100.00	$120.00	$140.00	$160.00
3	Test 2	$110.00	$130.00	$150.00	$170.00
4
5
6	Supplier	Month	Year
7	Test 1	Feb	2010
8
9
10	Totals:	10-Feb
11	Month	$120.00
12	Year	$2,520.00
...

Cell Formulas
Range	Formula
B10	=DATEVALUE(B7 & " 1, " & C7)
B11	=INDEX($1:$3,MATCH($A$7, $A$1:$A$3, 0), MATCH($B$10,$1:$1, 0))
B12	=INDEX($1:$3,MATCH($A$7, $A$1:$A$3, 0), MATCH("Total For "&$C$7,$1:$1, 0))

The headers in row 1 for each Year-Month are actual dates of the 1st of each month formatted to display as "yy-mmm".

The INDEX - MATCH - MATCH function

NOTE: The data to the right of column E is not displayed.

 

[Craig4670]

Thanks for your help! I'll try it out.