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

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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?
 
Upvote 0
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?
 
Upvote 0
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!
 
Upvote 0
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!
 
Upvote 0
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... :)
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

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
Back
Top