Page 1 of 2 1 2 LastLast
Results 1 to 10 of 17

Thread: Multiple Matches on VLOOKUP

  1. #1

    Multiple Matches on VLOOKUP



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Files Attached Files

  2. #2
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,235
    Articles
    0
    Excel Version
    Office 365 Subscription
    Could you provide the sheet with your attempted formula in place and a list of expected outcomes?

    Thanks.
    Ali
    Enthusiastic self-taught user of MS Excel!

  3. #3
    The file is attached right?

    Canīt you acess it?

    Thanks for the reply

  4. #4
    Can someone please hepl me?

  5. #5
    Administrator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,662
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Please be patient and leave members some time to react. FREE forums generally ask to wait about 24 hrs before bumping. Thanks
    Thank you Ken for this secure forum.

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

  7. #7
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,235
    Articles
    0
    Excel Version
    Office 365 Subscription
    See if this works for you:

    =IFERROR(VLOOKUP(EDATE($B10,-12),Sheet2!A:C,2,FALSE),"")
    Ali
    Enthusiastic self-taught user of MS Excel!

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

    Thanks for the reply

  9. #9
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,235
    Articles
    0
    Excel Version
    Office 365 Subscription
    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.
    Ali
    Enthusiastic self-taught user of MS Excel!

  10. #10
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,235
    Articles
    0
    Excel Version
    Office 365 Subscription
    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 by AliGW; 2016-08-17 at 02:42 PM.
    Ali
    Enthusiastic self-taught user of MS Excel!

Page 1 of 2 1 2 LastLast

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •