# Multiple Matches on VLOOKUP

#### nunofrcds

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

Thanks.

#### nunofrcds

##### New member
The file is attached right?

Can´t you acess it?

Thanks for the reply

#### nunofrcds

##### New member
Can someone please hepl me?

#### Pecoflyer

##### Admin Alumnus
Please be patient and leave members some time to react. FREE forums generally ask to wait about 24 hrs before bumping. Thanks

#### nunofrcds

##### New member
Sorry,
I am in a hurry, but I wait no problem

#### AliGW

##### Administrator
See if this works for you:

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

#### nunofrcds

##### New member
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
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
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
It doesn't work either, it gives me #N/A error

Thanks again for helping me

#### AliGW

##### Administrator
It works here. Are you sure you copied it correctly? Try again.

#### AliGW

##### Administrator
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
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

#### nunofrcds

##### New member
How can I check this tread as solved?

#### AliGW

##### Administrator
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
By the way, B2-366 is only correct for dates up to Leap Year Day. EDATE takes leap years into account.