# vlookup/match/array?

#### jkeyes

##### Active Member
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

##### MrExcel MVP
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
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
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
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
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...

Replies
1
Views
126
Replies
10
Views
649
Replies
2
Views
578
Replies
10
Views
737
Replies
40
Views
4K

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.

### Which adblocker are you using?

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

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