How to Vary Date value and keep the rest Constant

[nickysmithnicky]

Hi All

Would love your help with my following situation. I have been struggling with this for the last 3 days and have finally come close enough(or so I think!)

I have a reference table with columns as shown in TABLE A.
(DATE not= unique values and NUMBER not= unique values).

I want the result to look like TABLE B.

Based on the combined fields of the Date and Number(in table A), I want the corresponding

Amount value to be displayed in columns 1, 2 and 3(in table B).

I tried the following formula:

=INDEX($B$2:$B$10, MATCH(DATE(2010,1,1),$A$2:$A$10,0)*MATCH(J1, $C$2:$C$10,0))

The above works but just for one cell. I would like the DATE function to change(based on the date in column I) and keep the rest of the formula as it is.

Guys, am I even on the right track here ??

Any suggestions?

 

[Jonmo1]

Welcome to the board...

Try this, assuming the dates in column I are real Excel Dates.

=INDEX($B$2:$B$10, MATCH(I1,$A$2:$A$10,0)*MATCH(J1, $C$2:$C$10,0))

Where I1 = the date Jan 1 2011

 

[nickysmithnicky]

Thanks, that seems to work. However, when I try copying the formula down to the rest of the fields, I get the error, "#REF!".

I am unable to attach my excel file hence here is a view of the table structure.

TABLE A

A B C I J K L
Date Amount Number
11/19/2010 300 1
11/12/2010 250 2
11/19/2010 300 2
11/26/2010 350 2
11/12/2010 250 3
11/19/2010 300 3
11/26/2010 350 3
12/03/2010 430 3

TABLE B
Date_Unique 1 2 3
11/12/2010 250 250 250
11/19/2010 300 300 300
11/26/2010 *** 350 350
12/03/2010 *** *** 430

I am not sure if it is because of the duplicate values in "A"(date column) that I am getting this error. If so, how do I resolve?

 

[nickysmithnicky]

Here is the formula:

=INDEX($B$2:$B$10, MATCH(I2,$A$2:$A$10,0)*MATCH($K$1, $C$2:$C$10,0))

I used the formula in Table B, Column 2 where you can see the value as '250'. Then when I dragged down the formula to the following cells, I got the REF! error.

P.S: Here is the formula that got copied in on drag for the cell below:

=INDEX($B$2:$B$10, MATCH(I3,$A$2:$A$10,0)*MATCH($K$1, $C$2:$C$10,0))

 

[Jonmo1]

I think I missunderstood the formula/question...

Try this entered with CTRL + SHIFT + ENTER

=INDEX(B2:B10,MATCH(I1&"|"&J1,A2:A10&"|"&C2:C10,0))

 

[nickysmithnicky]

The result = "#N/A'. When I double click on the #N/A, it then changes to "#VALUE!"

Also, can you please tell me what your code meant?

 

[Jonmo1]

Use Excel Jeanie to post a sample of your data..
My signature has a link to the program.

 

[nickysmithnicky]

http://www.excel-jeanie-html.de/index.php?f=1]Excel tables to the web - Excel Jeanie Html 4

 

[Jonmo1]

After you click "Forum Standard" in Excel Jeanie
You can just paste into the forum reply.
You don't need to copy anything, the jeanie already copied it for you..
It will paste a bunch of code that looks like gibberish.
But when you submit the replay, it will display nicely.

 

[nickysmithnicky]

[FONT=&quot]Btw, thank you for that additional note on how to paste it! It drove me nuts.

Sheet1

<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 83px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td> </td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td><td>G</td><td>H</td><td>I</td><td>J</td><td>K</td><td>L</td><td>M</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td style="background-color: rgb(204, 255, 255); font-weight: bold;">Date</td><td style="background-color: rgb(204, 255, 255); font-weight: bold;">Amount</td><td style="background-color: rgb(204, 255, 255); font-weight: bold;">Number</td><td> </td><td> </td><td> </td><td> </td><td> </td><td style="background-color: rgb(204, 255, 204); font-weight: bold;">Date_Unique</td><td style="background-color: rgb(204, 255, 204); font-weight: bold; text-align: right;">1</td><td style="background-color: rgb(204, 255, 204); font-weight: bold; text-align: right;">2</td><td style="background-color: rgb(204, 255, 204); font-weight: bold; text-align: right;">3</td><td> </td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td style="background-color: rgb(204, 255, 255); text-align: right;">1/1/2010</td><td style="background-color: rgb(204, 255, 255); text-align: right;">250</td><td style="background-color: rgb(204, 255, 255); text-align: right;">1</td><td> </td><td> </td><td> </td><td> </td><td> </td><td style="text-align: right;">1/1/2010</td><td style="text-align: right;">250</td><td style="text-align: right;">250</td><td> </td><td> </td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td style="background-color: rgb(204, 255, 255); text-align: right;">1/2/2010</td><td style="background-color: rgb(204, 255, 255); text-align: right;">300</td><td style="background-color: rgb(204, 255, 255); text-align: right;">1</td><td> </td><td> </td><td> </td><td> </td><td> </td><td style="text-align: right;">1/2/2010</td><td style="text-align: right;">300</td><td>#VALUE!</td><td> </td><td> </td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td style="background-color: rgb(204, 255, 255); text-align: right;">1/3/2010</td><td style="background-color: rgb(204, 255, 255); text-align: right;">350</td><td style="background-color: rgb(204, 255, 255); text-align: right;">1</td><td> </td><td> </td><td> </td><td> </td><td> </td><td style="text-align: right;">1/3/2010</td><td style="text-align: right;">350</td><td> </td><td> </td><td> </td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td><td style="background-color: rgb(204, 255, 255); text-align: right;">1/4/2010</td><td style="background-color: rgb(204, 255, 255); text-align: right;">400</td><td style="background-color: rgb(204, 255, 255); text-align: right;">1</td><td> </td><td> </td><td> </td><td> </td><td> </td><td style="text-align: right;">1/4/2010</td><td style="text-align: right;">400</td><td> </td><td> </td><td> </td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td><td style="background-color: rgb(204, 255, 255); text-align: right;">1/1/2010</td><td style="background-color: rgb(204, 255, 255); text-align: right;">250</td><td style="background-color: rgb(204, 255, 255); text-align: right;">2</td><td> </td><td> </td><td> </td><td> </td><td> </td><td style="text-align: right;">1/5/2010</td><td> </td><td> </td><td> </td><td> </td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td><td style="background-color: rgb(204, 255, 255); text-align: right;">1/2/2010</td><td style="background-color: rgb(204, 255, 255); text-align: right;">300</td><td style="background-color: rgb(204, 255, 255); text-align: right;">2</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td><td style="background-color: rgb(204, 255, 255); text-align: right;">1/3/2010</td><td style="background-color: rgb(204, 255, 255); text-align: right;">350</td><td style="background-color: rgb(204, 255, 255); text-align: right;">2</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td><td style="background-color: rgb(204, 255, 255); text-align: right;">1/4/2010</td><td style="background-color: rgb(204, 255, 255); text-align: right;">400</td><td style="background-color: rgb(204, 255, 255); text-align: right;">2</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td><td style="background-color: rgb(204, 255, 255); text-align: right;">1/5/2010</td><td style="background-color: rgb(204, 255, 255); text-align: right;">450</td><td style="background-color: rgb(204, 255, 255); text-align: right;">2</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">11</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">12</td><td colspan="3" style="background-color: rgb(204, 255, 255);"> TABLE A</td><td> </td><td colspan="3"> Should look like </td><td> </td><td> </td><td colspan="3" style="background-color: rgb(204, 255, 204);"> TABLE B</td><td> </td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">13</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">14</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr></tbody></table>
<table style="font-family: Arial; font-size: 10pt; border-style: groove; border-color: rgb(0, 255, 0); background-color: rgb(255, 252, 249); color: rgb(0, 0, 0);"><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family: Arial; font-size: 9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color: rgb(202, 202, 202); font-size: 10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>J2</td><td>=INDEX($B$2:$B$10, MATCH(I2,$A$2:$A$10,0)*MATCH($J$1, $C$2:$C$10,0))</td></tr><tr><td>K2</td><td>=INDEX($B$2:$B$10, MATCH(I2,$A$2:$A$10,0)*MATCH($K$1, $C$2:$C$10,0))</td></tr><tr><td>J3</td><td>=INDEX($B$2:$B$10, MATCH(I3,$A$2:$A$10,0)*MATCH($J$1, $C$2:$C$10,0))</td></tr><tr><td>K3</td><td>=INDEX(B2:B10,MATCH(I1&"|"&J1,A2:A10&"|"&C2:C10,0))</td></tr><tr><td>J4</td><td>=INDEX($B$2:$B$10, MATCH(I4,$A$2:$A$10,0)*MATCH($J$1, $C$2:$C$10,0))</td></tr><tr><td>J5</td><td>=INDEX($B$2:$B$10, MATCH(I5,$A$2:$A$10,0)*MATCH($J$1, $C$2:$C$10,0))</td></tr></tbody></table></td></tr></tbody></table>
Excel tables to the web - Excel Jeanie Html 4


[/FONT]