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]

Try this in K2 and Filled Right/Down to L6
=INDEX($C$2:$C$10,MATCH($J2&"|"&K$1,$B$2:$B$10&"|"&$D$2:$D$10,0))

It will be #N/A in K6 because there is no match for 1 and 1/5/2010

IMPORTANT
This is an array formula that required CTRL + SHIFT + ENTER
After entering formula in K2, Highlight K2 and press F2
Then press CTRL + SHIFT + ENTER
When entered correctly, the formula will be enclosed in {Brackets}

Then fill formula to L6

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	1	Date	Amount	Number						Date_Unique	1	2	3
2	2	1/1/2010	250	1						1/1/2010	250	250
3	3	1/2/2010	300	1						1/2/2010	300	300
4	4	1/3/2010	350	1						1/3/2010	350	350
5	5	1/4/2010	400	1						1/4/2010	400	400
6	6	1/1/2010	250	2						1/5/2010	#N/A	450
7	7	1/2/2010	300	2
8	8	1/3/2010	350	2
9	9	1/4/2010	400	2
10	10	1/5/2010	450	2
Sheet1

 

[nickysmithnicky]

This works perfectly! Thanks!!

(1) Can this be auto-generated every time I open the excel file?

What we do is, export the data(the daily counts logged into our office application) into an excel file for reporting purpose. Once we have the excel file, I would like the code to run automatically for all the new data as well.


(2 a) Can you please tell me what doesthe pipe, "|" do?
(2 b) How does $D$2 differ from $D2 ?

 

[Jonmo1]

1) Likely yes, I'd suggest making a new thread for that..

2a) The pipe is a bit of overkill, probably not required in this case.
But my motto is "Better to have and not need, then to need and not have"

Basically, we are concatenating 2 cells together, say K1 and J2
Now, consider
K1 = 1234
J2 = 5678

And you're looking for exactly that, 12345678
Now, if
K1 = 123
J2 = 45678

It would still find the match K1&J2 = 12345678
So putting the pipe in the middle fixes that because now
K1&"|"&J2 = 123|45678
But you're trying to find 1234|5678


2b) The $ in a reference locks that reference. So that it does not incriment when filling formula left/right/up/down.
D2 changes to D2 when filled down
D2 changes to E2 when filled right
$D2 STAYS as $D2 when filled Right
D$2 STAYS as D$2 when filled down
$D$2 does not change no matter which way you fill left/right/up/down


Hope that helps.

 

[nickysmithnicky]

Thanks! That was very helpful.


Actually just one more question which I forgot to ask earlier: - What does Ctrl+Shift+Enter do to make the formula work?


I will start a new thread because I have to see this project through completion


Thank you once again. I really appreciate your timely help.