Formula Help (IF? DSUM? LOOKUP?)

garymu16

Board Regular
Joined
Sep 15, 2006
Messages
139
Book2
ABCDEFGHIJK
1TableAPR
2SalesMonthYTD
3ProductA14
4ProductB28
5ProductC312
6
7DataMonthCumulative
8Jan-06Feb-06Mar-06Apr-06Jan-06Feb-06Mar-06Apr-06
9ProductA11111234
10ProductB22222468
11ProductC333336912
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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
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

Board Regular
Joined
Sep 23, 2006
Messages
202
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:
Code:
=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:
Code:
=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

Board Regular
Joined
Sep 15, 2006
Messages
139
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

Board Regular
Joined
Sep 15, 2006
Messages
139

ADVERTISEMENT

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

Legend
Joined
Feb 18, 2002
Messages
13,690
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

Board Regular
Joined
Sep 15, 2006
Messages
139

ADVERTISEMENT

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

Legend
Joined
Feb 18, 2002
Messages
13,690
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

Board Regular
Joined
Sep 15, 2006
Messages
139
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.
 

Forum statistics

Threads
1,136,655
Messages
5,677,017
Members
419,668
Latest member
DharmaK

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?

Disable AdBlock

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
Top