# Thread: Multiple Matches on VLOOKUP

1. ## Multiple Matches on VLOOKUP

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

2. Could you provide the sheet with your attempted formula in place and a list of expected outcomes?

Thanks.

3. The file is attached right?

Canīt you acess it?

4. Can someone please hepl me?

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

6. Sorry,
I am in a hurry, but I wait no problem

7. See if this works for you:

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

8. 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?

9. 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.

10. 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))

Page 1 of 2 1 2 Last