Multiple Matches on VLOOKUP

N

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
Could you provide the sheet with your attempted formula in place and a list of expected outcomes?

Thanks.
 
The file is attached right?

Can´t you acess it?

Thanks for the reply
 
Please be patient and leave members some time to react. FREE forums generally ask to wait about 24 hrs before bumping. Thanks
 
See if this works for you:

=IFERROR(VLOOKUP(EDATE($B10,-12),Sheet2!A:C,2,FALSE),"")
 
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 :)
 
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. :)
 
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:
It doesn't work either, it gives me #N/A error

Thanks again for helping me :)
 
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)))),"")
 
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 :)
 
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!
 
By the way, B2-366 is only correct for dates up to Leap Year Day. EDATE takes leap years into account. :)
 
You must log in or register to reply here.
Back
Top