Vlookup repeated dates

5 pts.
Tags:
Excel 2007
VLOOKUP function
I have a spreadsheet (A) with various data, where one column contains variuos dates, and one column with values. I want to be able to distinguish all values for each year, so I have another spreadsheet (B) with a long list of all dates for 2010, 2011 and 2012. 
I use vlookup (VLOOKUP(F3;$B$3:$C$7;2;FALSE) to search for each date in the second sheet to find a match in the first sheet, and return the corresponding values. That works fine. 
The problem is that in the first sheet, the same date can occur more than once, with different values, while the second sheet only searches for the dates once. That means only one value for the repeated dates will be counted.
Sheet (A) [table style="border-collapse: collapse; width: 103pt;" width="137" cellspacing="0" cellpadding="0" border="0"> [colgroup][col style="mso-width-source: userset; mso-width-alt: 2669; width: 55pt;" width="73">[/cOLIST] [col style="width: 48pt;" width="64">[/cOLIST] [/colgroup] [tbody] [tr style="height: 15.0pt;" height="20"> [td style="height: 15.0pt; width: 55pt;" width="73" align="right" class="xl63" height="20">2010-02-17[/td] [td style="width: 48pt;" width="64" align="right">2000[/td] [/tr] [tr style="height: 15.0pt;" height="20"> [td style="height: 15.0pt;" align="right" class="xl63" height="20">2011-11-12[/td] [td align="right">1000[/td] [/tr] [tr style="height: 15.0pt;" height="20"> [td style="height: 15.0pt;" align="right" class="xl63" height="20">2010-02-17[/td] [td align="right">5000[/td] [/tr] [tr style="height: 15.0pt;" height="20"> [td style="height: 15.0pt;" align="right" class="xl63" height="20">2010-02-18[/td] [td align="right">300[/td] [/tr] [tr style="height: 15.0pt;" height="20"> [td style="height: 15.0pt;" align="right" class="xl63" height="20">2011-11-13[/td] [td align="right">600[/td] [/tr] [/tbody] [/table] Sheet (B) [table style="border-collapse: collapse; width: 107pt;" width="142" cellspacing="0" cellpadding="0" border="0"> [colgroup][col style="mso-width-source: userset; mso-width-alt: 2669; width: 55pt;" width="73">[/cOLIST] [col style="mso-width-source: userset; mso-width-alt: 2523; width: 52pt;" width="69">[/cOLIST] [/colgroup] [tbody] [tr style="height: 15.0pt;" height="20"> [td style="height: 15.0pt; width: 55pt;" width="73" align="right" class="xl65" height="20">2010-02-16[/td] [td style="width: 52pt;" width="69" align="right">0[/td] [/tr] [tr style="height: 15.0pt;" height="20"> [td style="height: 15.0pt;" align="right" class="xl65" height="20">2010-02-17[/td] [td align="right">2000[/td] [/tr] [tr style="height: 15.0pt;" height="20"> [td style="height: 15.0pt;" align="right" class="xl65" height="20">2010-02-18[/td] [td align="right">300[/td] [/tr] [tr style="height: 15.0pt;" height="20"> [td style="height: 15.0pt;" align="right" class="xl65" height="20">2011-11-12[/td] [td align="right">1000[/td] [/tr] [tr style="height: 15.0pt;" height="20"> [td style="height: 15.0pt;" align="right" class="xl65" height="20">2011-11-13[/td] [td align="right">600[/td] [/tr] [/tbody] [/table]
I have tried cross-searching, by first adding a column to the first spreadsheet, giving all dates a unique number: 1,2,3,4 and so on. After that, I have used vlookup on those numbers, getting all dates as result. That way I get the repeated dates as a search reference. However, when using these dates as startpoint for a second vlookup, all repeated dates get the value of the same date (see below, missing 5000).
Sheet (A)
[table style="border-collapse: collapse; width: 151pt;" width="201" cellspacing="0" cellpadding="0" border="0"> [colgroup][col style="width: 48pt;" width="64">[/cOLIST] [col style="mso-width-source: userset; mso-width-alt: 2669; width: 55pt;" width="73">[/cOLIST] [col style="width: 48pt;" width="64">[/cOLIST] [/colgroup] [tbody] [tr style="height: 15.0pt;" height="20"> [td style="height: 15.0pt; width: 48pt;" width="64" align="right" height="20">1[/td] [td style="width: 55pt;" width="73" align="right" class="xl65">2010-02-17[/td] [td style="width: 48pt;" width="64" align="right">2000[/td] [/tr] [tr style="height: 15.0pt;" height="20"> [td style="height: 15.0pt;" align="right" height="20">2[/td] [td align="right" class="xl65">2011-11-12[/td] [td align="right">1000[/td] [/tr] [tr style="height: 15.0pt;" height="20"> [td style="height: 15.0pt;" align="right" height="20">3[/td] [td align="right" class="xl65">2010-02-17[/td] [td align="right">5000[/td] [/tr] [tr style="height: 15.0pt;" height="20"> [td style="height: 15.0pt;" align="right" height="20">4[/td] [td align="right" class="xl65">2010-02-18[/td] [td align="right">300[/td] [/tr] [tr style="height: 15.0pt;" height="20"> [td style="height: 15.0pt;" align="right" height="20">5[/td] [td align="right" class="xl65">2011-11-13[/td] [td align="right">600[/td] [/tr] [/tbody] [/table]
Sheet (B)
[table style="border-collapse: collapse; width: 151pt;" width="201" cellspacing="0" cellpadding="0" border="0"> [colgroup][col style="width: 48pt;" width="64">[/cOLIST] [col style="mso-width-source: userset; mso-width-alt: 2669; width: 55pt;" width="73">[/cOLIST] [col style="width: 48pt;" width="64">[/cOLIST] [/colgroup] [tbody] [tr style="height: 15.0pt;" height="20"> [td style="height: 15.0pt; width: 48pt;" width="64" align="right" height="20">1[/td] [td style="width: 55pt;" width="73" align="right" class="xl65">2010-02-17[/td] [td style="width: 48pt;" width="64" align="right">2000[/td] [/tr] [tr style="height: 15.0pt;" height="20"> [td style="height: 15.0pt;" align="right" height="20">2[/td] [td align="right" class="xl65">2011-11-12[/td] [td align="right">1000[/td] [/tr] [tr style="height: 15.0pt;" height="20"> [td style="height: 15.0pt;" align="right" height="20">3[/td] [td align="right" class="xl65">2010-02-17[/td] [td align="right">2000[/td] [/tr] [tr style="height: 15.0pt;" height="20"> [td style="height: 15.0pt;" align="right" height="20">4[/td] [td align="right" class="xl65">2010-02-18[/td] [td align="right">300[/td] [/tr] [tr style="height: 15.0pt;" height="20"> [td style="height: 15.0pt;" align="right" height="20">5[/td] [td align="right" class="xl65">2011-11-13[/td] [td align="right">600[/td] [/tr] [/tbody] [/table]
Is there a way to solve this?
Thanks in advance!


Software/Hardware used:
Excel 2007
ASKED: February 19, 2012  11:41 AM
UPDATED: February 27, 2012  8:11 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following