Need help with  VLOOKUP by WEEKNUM

    Hi, I'm trying to pull data in by using "=WEEKNUM()" in the Lookup_value but keep getting a #N/A return. I Was able to use TODAY() in a lookup but can't seem to get WEEKNUM to work, any advice?


    Thanks in Advance,
    Chris





    [ATTACH=JSON]{"data-align":"none","data-size":"full","title":"Capture.PNG","data-attachmentid":1224890}[/ATTACH]
    [ATTACH=JSON]{"data-align":"none","data-size":"full","title":"Capture.PNG","data-attachmentid":1224891}[/ATTACH]

    Hello,


    Why don't you attach your file ...


    Would make everything easier ... :smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Hi again,


    Most probably, you are looking for the right formula to go to cell B28 in your sheet ' Weekly Actions ' ...


    Attached is a proposal ... where you do need in cell A26 to input a reference date for the formula to operate correctly ...


    Hope this will help

    Hi Carim,
    This is a little beyond my current skill set so please forgive my ignorance. Will this change the dataset based on the current week number?
    Oh and thanks for the spell correct... it was bothering me :)

    If in cell A26 you input =Today() ...


    you will get automatically get the current week number ... in your formula ...


    With the A26 cell, you retain the flexibility to ask for another week than the current one ...


    If you do not need this flexibility, you can use following formula in cell B28 :


    Code
    =INDEX(Data!$A$1:$M$601,MATCH(WEEKNUM(TODAY()),Data!$B:$B,0),MATCH(A28,Data!$1:$1,0))


    This formula can be copied down till B34 ...


    then, you can delete cell A26 ...


    Hope this clarifies

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    By the way, the Index & Match combination is a lot more flexible then Vlookup() ...


    For a very good explanation, you can take a look at :


    https://exceljet.net/index-and-match


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    To be on the safe side ... attached is your Version 2 ...


    Both Sheets ' Daily Actions ' and ' Weekly Actions ' modified ... for you to use the very same formula in both Columns B ...


    Hope this will help

    Again, thank you so much Carim, excuse my slowness as I'm still drinking my coffee but the second spreadsheet doesn't return any data for weekly actions? I've never used the Index and Match function but am working through the link you provided. Where has this been all my life?

    Hello,


    Just noticed a mistake in your sheet ' Data ' ... in cell B9 ...


    your formula, to be consistent with your structure should be


    Code
    =WEEKNUM(A8,1)


    Hope this helps ...

    Apparently ... it did fix your problem ...


    Thanks for your Like

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login