vlookup/match/array?

jkeyes

Active Member
Joined
Apr 15, 2003
Messages
343
I had a similar question about a month ago, but it's just different enough to be causing me headaches...

What I am trying to do is develop a formula for the shaded cells on Sheet1 that returns the cell values by Unique ID, matched by month, from Sheet2 (I've left the desired results in the shaded area for illustration). As you can see, the results will come in a "waterfall" pattern on Sheet1 due to the Unique IDs staggered monthly Issue Dates. But, the source data (Sheet2) is arranged based on the life-cycle of each Unique ID (Col. 0 = Issue Date minus 1 Month, Col. 1 = Issue Date Month, and Col. 2 = Issue Date plus 1 Month, etc.).

Although this is an extremely pared-down version of my actual problem, I think I've provided enough info here, but please let me know if you need more... thanks in advance for any and all help!

Sheet1:
PPC ME Example.xls
ABCDEFGH
1UniqueIDIssueDate08/01/0609/01/0610/01/0611/01/0612/01/0601/01/07
21234509/01/062102354
32345609/01/06381645
43456710/01/062111943
54567811/01/061152239
Sheet1


Sheet2:
PPC ME Example.xls
ABCDEF
1UniqueIDIssueDate0123
21234509/01/062102354
32345609/01/06381645
43456710/01/062111943
54567811/01/061152239
Sheet2
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Why don't you have the more reasonable set of -1, 0, 1, 2 instead of the current set of 0, 1, 2, 3 in C1:F1 on Sheet2?
 

jkeyes

Active Member
Joined
Apr 15, 2003
Messages
343
Why don't you have the more reasonable set of -1, 0, 1, 2 instead of the current set of 0, 1, 2, 3 in C1:F1 on Sheet2?

Well, if I were to start the project from the outset now, that is how I'd approach it. But as I'm sure you know, these things evolve over time and often before, or outside, your control. The unfortunate reality is that this is the way the data is set up.

So, given that, any ideas for a solution?
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,697
If the Unique ID occurs only once in the source data, maybe...

Sheet1!C2, copied across and down:

Code:
=SUM(IF(Sheet2!$A$2:$A$5=$A2,IF(DATE(YEAR(Sheet2!$B$2:$B$5),MONTH(Sheet2!$B$2:$B$5)+Sheet2!$C$1:$F$1-1,1)=C$1,Sheet2!$C$2:$F$5)))

Otherwise...

Code:
=SUM(IF(Sheet2!$A$2:$A$5=$A2,IF(Sheet2!$B$2:$B$5=$B2,IF(DATE(YEAR(Sheet2!$B$2:$B$5),MONTH(Sheet2!$B$2:$B$5)+Sheet2!$C$1:$F$1-1,1)=C$1,Sheet2!$C$2:$F$5))))

Note that these formulas need to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Also, to hide zero values, select Sheet1!C2:H5 and format these cells as...

0;-0;;@

Hope this helps!
 

jkeyes

Active Member
Joined
Apr 15, 2003
Messages
343
Thanks Domenic... that looks like it's going to work!!!

And thanks for the formatting tip... I'm not sure I understand why that custom format works... would you mind explaining?

Thanks again!
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,697
Thanks Domenic...

You're very welcome! Glad I could help!

...I'm not sure I understand why that custom format works... would you mind explaining?

Actually, I don't quite understand either. Maybe someone else here can explain it... :)
 

Forum statistics

Threads
1,136,502
Messages
5,676,225
Members
419,614
Latest member
bfletcher

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