Formula Help (IF? DSUM? LOOKUP?)

[garymu16]

Book2
	A	B	C	D	E	F	G	H	I	J	K
1	Table			APR
2		Sales	Month	YTD
3		ProductA	1	4
4		ProductB	2	8
5		ProductC	3	12
6
7	Data		Month					Cumulative
8			Jan-06	Feb-06	Mar-06	Apr-06		Jan-06	Feb-06	Mar-06	Apr-06
9		ProductA	1	1	1	1		1	2	3	4
10		ProductB	2	2	2	2		2	4	6	8
11		ProductC	3	3	3	3		3	6	9	12
Sheet1

I am trying to set up a spreadsheet which will read, in this example, cell D1, lookup the product name and return the values for the appropriate month & YTD, based on the data entry tables.

Can anyone help out?

P.S. Cell D1 should read Apr-06 not APR as in my example - Apologies

 

[acw]

Hi

B2: =INDEX($C$9:$F$11,MATCH(B3,$B$9:$B$11,0),MATCH($D$1,$C$8:$F$8,0))
C2: =INDEX($H$9:$K$11,MATCH(B3,$B$9:$B$11,0),MATCH($D$1,$H$8:$K$8,0))

Adjust the ranges to suit.


HTH

Tony

 

[Erdinç E. Karaçam]

Hi,


<table border=1><tr><td align=left valign=center><font size=1 color=red face=verdana>Gönderen XL Ver.:</font><font size=1 face=verdana> Office XP / </font><font size=1 color=red face=verdana>OS Ver.:</font><font size=1 face=verdana> Windows XP </font></td></tr><tr valign=top><td><table border=0 bgcolor=d4d0c8 cellspacing=1 cellpadding=1 align=center><tr align=center valign=center bgcolor=white><td bgcolor=d4d0c8 align=center><font color=black size=2>+</font></td><td bgcolor=d4d0c8 width=60><font color=black size=1 face=verdana>A</font></td><td bgcolor=d4d0c8 width=60><font color=black size=1 face=verdana>B</font></td><td bgcolor=d4d0c8 width=60><font color=black size=1 face=verdana>C</font></td><td bgcolor=d4d0c8 width=60><font color=black size=1 face=verdana>D</font></td><td bgcolor=d4d0c8 width=60><font color=black size=1 face=verdana>E</font></td><td bgcolor=d4d0c8 width=60><font color=black size=1 face=verdana>F</font></td><td bgcolor=d4d0c8 width=60><font color=black size=1 face=verdana>G</font></td><td bgcolor=d4d0c8 width=60><font color=black size=1 face=verdana>H</font></td><td bgcolor=d4d0c8 width=60><font color=black size=1 face=verdana>I</font></td><td bgcolor=d4d0c8 width=60><font color=black size=1 face=verdana>J</font></td><td bgcolor=d4d0c8 width=60><font color=black size=1 face=verdana>K</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>1</font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>Table</font></td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>APR</font></td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>2</font></td><td bgcolor=white></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>Sales</font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>Month</font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>YTD</font></td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>3</font></td><td bgcolor=white></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>Product A</font></td><td bgcolor=white nowrap=true align=center><font size=1 color=blue face=verdana><ACRONYM title='=DÜŞEYARA(B3;$B$9:$F$11;KAÇINCI(YATAYARA(YAZIM.DÜZENİ($D$1&"-06");$C$8:$F$8;1;0);$C$8:$F$8;0)+1;0)'>55 (ƒx)</ACRONYM></font></td><td bgcolor=white nowrap=true align=center><font size=1 color=blue face=verdana><ACRONYM title='=DÜŞEYARA(B3;$B$9:$K$11;KAÇINCI(YATAYARA(YAZIM.DÜZENİ($D$1&"-06");$H$8:$K$8;1;0);$H$8:$K$8;0)+6;0)'>44 (ƒx)</ACRONYM></font></td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>4</font></td><td bgcolor=white></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>Product B</font></td><td bgcolor=white nowrap=true align=center><font size=1 color=blue face=verdana><ACRONYM title='=DÜŞEYARA(B4;$B$9:$F$11;KAÇINCI(YATAYARA(YAZIM.DÜZENİ($D$1&"-06");$C$8:$F$8;1;0);$C$8:$F$8;0)+1;0)'>66 (ƒx)</ACRONYM></font></td><td bgcolor=white nowrap=true align=center><font size=1 color=blue face=verdana><ACRONYM title='=DÜŞEYARA(B4;$B$9:$K$11;KAÇINCI(YATAYARA(YAZIM.DÜZENİ($D$1&"-06");$H$8:$K$8;1;0);$H$8:$K$8;0)+6;0)'>88 (ƒx)</ACRONYM></font></td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>5</font></td><td bgcolor=white></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>Product C</font></td><td bgcolor=white nowrap=true align=center><font size=1 color=blue face=verdana><ACRONYM title='=DÜŞEYARA(B5;$B$9:$F$11;KAÇINCI(YATAYARA(YAZIM.DÜZENİ($D$1&"-06");$C$8:$F$8;1;0);$C$8:$F$8;0)+1;0)'>77 (ƒx)</ACRONYM></font></td><td bgcolor=white nowrap=true align=center><font size=1 color=blue face=verdana><ACRONYM title='=DÜŞEYARA(B5;$B$9:$K$11;KAÇINCI(YATAYARA(YAZIM.DÜZENİ($D$1&"-06");$H$8:$K$8;1;0);$H$8:$K$8;0)+6;0)'>122 (ƒx)</ACRONYM></font></td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>6</font></td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>7</font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>Data</font></td><td bgcolor=white></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>Month</font></td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>Cumulative</font></td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>8</font></td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>Jan-06</font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>Feb-06</font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>Mar-06</font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>Apr-06</font></td><td bgcolor=white></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>Jan-06</font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>Feb-06</font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>Mar-06</font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>Apr-06</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>9</font></td><td bgcolor=white></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>Product A</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>1</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>2</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>3</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>55</font></td><td bgcolor=white></td><td bgcolor=white nowrap=true align=right><font size=1 color=blue face=verdana>1</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=blue face=verdana>2</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=blue face=verdana>3</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=blue face=verdana>44</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>10</font></td><td bgcolor=white></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>Product B</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>5</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>6</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>7</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>66</font></td><td bgcolor=white></td><td bgcolor=white nowrap=true align=right><font size=1 color=blue face=verdana>2</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=blue face=verdana>4</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=blue face=verdana>6</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=blue face=verdana>88</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>11</font></td><td bgcolor=white></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>Product C</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>9</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>10</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>11</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>77</font></td><td bgcolor=white></td><td bgcolor=white nowrap=true align=right><font size=1 color=blue face=verdana>3</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=blue face=verdana>6</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=blue face=verdana>9</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=blue face=verdana>122</font></td></tr></table></td></tr><tr><td bgcolor=f0f8ff align=center><font color=black size=1 face=verdana>XLtoHTML v1.1 / ExcelTürkiye - 2006©</font></td></tr></table>


C3:

=VLOOKUP(B3;$B$9:$F$11;MATCH(HLOOKUP(PROPER($D$1&"-06");$C$8:$F$8;1;0);$C$8:$F$8;0)+1;0)

Fill down to C5


And;
D3:

=VLOOKUP(B3;$B$9:$K$11;MATCH(HLOOKUP(PROPER($D$1&"-06");$H$8:$K$8;1;0);$H$8:$K$8;0)+6;0)

Fill down to D5

Ok?

 

[garymu16]

Hi

B2: =INDEX($C$9:$F$11,MATCH(B3,$B$9:$B$11,0),MATCH($D$1,$C$8:$F$8,0))
C2: =INDEX($H$9:$K$11,MATCH(B3,$B$9:$B$11,0),MATCH($D$1,$H$8:$K$8,0))

Adjust the ranges to suit.


HTH

Tony

This formula works fine, apart from one thing................

When there are two lines for say, product A, this formula only appaers to bring one value thru'. It doesn't seem to consolidate the values?

Any idea's?

Thx

 

[garymu16]

Should have said if there was more than 1 "Product A" in the data.

It seems that "Match" is the problem as I do not think it can calculate more than one value. Is there another formula for this part?

 

[lenze]

Rearrange your data table into 3 columns only. Product Name, Date, and Quantity. Now it is a piece of cake to get your results using a Pivot Table.

lenze

 

[garymu16]

Hi lenze,

Wouldn't work unfortunatly.

The data presented here was purely to try and establish which formula I could use. The actual data is slightly more complex.

Thx

 

[lenze]

The Pivot Table is the most powerful tool available in Excel.

Bill Jelen AKA MrExcel
Pivot tables are the most powerful feature in Excel. A basic pivot table will allow you to summarize 50,000 rows of transactional data in seconds with just a few mouse clicks. White collar productivity would skyrocket if everyone knew how to use pivot tables.

Find it hard to believe it would NOT work in your situation

 

[garymu16]

Difficult or not to believe - It's not the answer to my problem - The formula previously provided is very close. The MATCH function being the issue I believe.

I use pivot tables daily, I know how good (great) they can be - Just not on this occasion.

 

[lenze]

Maybe you should buy Bill's book

http://www.mrexcel.com/sunshop/index.php?action=item&substart=0&id=75

lenze