Multiple Matches on VLOOKUP

nunofrcds

New member
Joined
Aug 12, 2016
Messages
14
Reaction score
0
Points
0
Hi guys,

I am having some problems with my VLOOKUP formula. I have to copy data in column B from sheet 2, and paste it in column D from sheet 1, but they have to be in the same day one year apart. For example I want to copy the information on column B from sheet 2 that is in the same row as the date 01-01-2016 to a cell on column D from sheet 1, that is in the same row as the date 01-01-2016.
I am using the formula,

=IF(MOD(C10-1;4)=0;VLOOKUP(B10-366;Sheet2!A:C;2;FALSE);VLOOKUP(B10-365;Sheet2!A:C;2;FALSE))

to check if the difference between the date is 366 days, or 365 days, and then copy. In column C from sheet 1, I am using the formula =YEAR(B2), to isolate the year and make the above formula distinguish from leap years and normal years. My problem is that I have several dates in column B from sheet 1 and column A from sheet 2 that are the same, and this formula only copies the first, and i want it to copy all. I attached a excel workbook that exemplifies my doubt.
Can you help me?

Thanks:)
 

Attachments

  • New Microsoft Excel Worksheet.xlsx
    14.5 KB · Views: 29

AliGW

Administrator
Joined
Nov 8, 2015
Messages
1,785
Reaction score
1
Points
38
Location
Ipswich, Suffolk, England
Excel Version(s)
MS 365 (Beta Insider Channel)
Could you provide the sheet with your attempted formula in place and a list of expected outcomes?

Thanks.
 

nunofrcds

New member
Joined
Aug 12, 2016
Messages
14
Reaction score
0
Points
0
The file is attached right?

Can´t you acess it?

Thanks for the reply
 

Pecoflyer

Admin Alumnus
Joined
Oct 13, 2011
Messages
1,766
Reaction score
0
Points
36
Location
Brussels Belgium
Excel Version(s)
2010 on Xubuntu - O365
Please be patient and leave members some time to react. FREE forums generally ask to wait about 24 hrs before bumping. Thanks
 

AliGW

Administrator
Joined
Nov 8, 2015
Messages
1,785
Reaction score
1
Points
38
Location
Ipswich, Suffolk, England
Excel Version(s)
MS 365 (Beta Insider Channel)
See if this works for you:

=IFERROR(VLOOKUP(EDATE($B10,-12),Sheet2!A:C,2,FALSE),"")
 

nunofrcds

New member
Joined
Aug 12, 2016
Messages
14
Reaction score
0
Points
0
It has the same problem as mine...
The formula is able to find the date but it only copies the first value, it only copies P_BCP, it doesn't copy P_NB that is one row bellow and has the same date associated.
Do you have any other solution?

Thanks for the reply :)
 

AliGW

Administrator
Joined
Nov 8, 2015
Messages
1,785
Reaction score
1
Points
38
Location
Ipswich, Suffolk, England
Excel Version(s)
MS 365 (Beta Insider Channel)
Sorry - I missed that!

You are probably going to need an array formula, with which I can't help you at the moment, but the EDATE function will at least make it shorter for whoever can. :)
 

AliGW

Administrator
Joined
Nov 8, 2015
Messages
1,785
Reaction score
1
Points
38
Location
Ipswich, Suffolk, England
Excel Version(s)
MS 365 (Beta Insider Channel)
Try this in D2 copied down (NOT an array formula):

=IF(INDEX(Sheet2!$B$2:$B$120,MATCH(EDATE($B2,-12),Sheet2!$A$2:$A$120,0)+COUNTIF($B$2:B2,B2)-1,0)=0,"",INDEX(Sheet2!$B$2:$B$120,MATCH(EDATE($B2,-12),Sheet2!$A$2:$A$120,0)+COUNTIF($B$2:B2,B2)-1,0))
 
Last edited:

nunofrcds

New member
Joined
Aug 12, 2016
Messages
14
Reaction score
0
Points
0
It doesn't work either, it gives me #N/A error

Thanks again for helping me :)
 

AliGW

Administrator
Joined
Nov 8, 2015
Messages
1,785
Reaction score
1
Points
38
Location
Ipswich, Suffolk, England
Excel Version(s)
MS 365 (Beta Insider Channel)
Apologies!

This longer formula really does work on your data:

=IFERROR(IF(INDEX(Sheet2!$B$2:$B$120,MATCH(EDATE($B2,-12),Sheet2!$A$2:$A$120,0))=0,"",IF(INDEX(Sheet2!$B$2:$B$120,MATCH(EDATE($B2,-12),Sheet2!$A$2:$A$120,0)+COUNTIF($B$2:B2,B2)-1)=0,"",IF(COUNTIF($B$2:B2,B2)=1,INDEX(Sheet2!$B$2:$B$120,MATCH(EDATE($B2,-12),Sheet2!$A$2:$A$120,0)),INDEX(Sheet2!$B$2:$B$120,MATCH(EDATE($B2,-12),Sheet2!$A$2:$A$120,0)+COUNTIF($B$2:B2,B2)-1)))),"")
 

nunofrcds

New member
Joined
Aug 12, 2016
Messages
14
Reaction score
0
Points
0
I created a formula of my own that also solves the problem

=IF(COUNTIF(Sheet2!B:B;B10-366)<ROW(Sheet1!B10)-MATCH(Sheet1!B10;Sheet1!B:B;0);"";IF(MATCH(B9-366;Sheet2!B:B;0)=MATCH(B10-366;Sheet2!B:B;0);INDEX(Sheet2!C:C;MATCH(Sheet1!B10-366;Sheet2!B:B;0)+ROW(Sheet1!B10)-MATCH(Sheet1!B10;Sheet1!B:B;0));INDEX(Sheet2!C:C;MATCH(Sheet1!B10-366;Sheet2!B:B;0))))

In my worksheet I already replaced the B10-366 by EDATE(B10;-12) like you suggested but the references are not the same anymore, so I only have this formula to show.

Thanks for your help, without you I wouldn't be able to use the functions COUNTIF and EDATE because I didn't knew them.

Thanks a lot :)
 

AliGW

Administrator
Joined
Nov 8, 2015
Messages
1,785
Reaction score
1
Points
38
Location
Ipswich, Suffolk, England
Excel Version(s)
MS 365 (Beta Insider Channel)
Collaborations are good, and anyway, we all learn a lot more by trying to adapt suggestions rather than expecting someone else to do it for us. That's how I've learnt pretty much everything I know. Thanks for letting me know and I am really glad that what I contributed helped you to find a solution. Bravo!
 

AliGW

Administrator
Joined
Nov 8, 2015
Messages
1,785
Reaction score
1
Points
38
Location
Ipswich, Suffolk, England
Excel Version(s)
MS 365 (Beta Insider Channel)
By the way, B2-366 is only correct for dates up to Leap Year Day. EDATE takes leap years into account. :)
 
Top