vlookup for 2 criteria from multiple columns that change daily

  • Hello, I have two sheets on excel. Sheet1 consists of data from other sheets so Sheet1 is the main Sheet to look at. I am having trouble finding a vlookup formula that can get the accurate information from Sheet2. On Sheet1, in column A I have item numbers and in row 1 I have dates that alternate weekly (16-Jul, 23-Jul, 30-Jul, 6-Aug, etc.). On Sheet1, for each weekly date and item number I get a numerical amount from Sheet2. Sheet2 has the same exact layout except instead of dates in row 1, there are week numbers ([starting from the example dates above] 29, 30, 31, 32, etc.). The week numbers change daily on Sheet2 so for example today it can be 29,34,45,46,51. Right now I have a large range to account for multiple possible future item numbers and weekly numbers andthe best I have is:


    =IF(ISNA(VLOOKUP(LEFT($A2,LEN($A2)),'Sheet2'!$A$2:$AZ$8000,COLUMN()-4,0)),"",VLOOKUP(LEFT(Summary!$A2,LEN(Summary!$A2)),'Sheet2'!$A$2:$AZ$8000,COLUMN()-4,0))


    but the problem lies with the "COLUMN()-4" as I do not know what this does nor does it capture the correct date because it gets the amount but it assumes that all possible dates on Sheet1 (16-Jul, 23-Jul, 30-Jul, 6-Aug, etc.) have no gaps when on Sheet2 there are because not all week numbers are listed. I cannot list them all on Sheet2, only whatever week number and item number is updated and appears, so there are gaps (whereas Sheet1 has every date and whatever does not appear is just left as a blank cell since it is not relevant). Hope this makes sense! The workbook is saved daily with a new date.


    Is there a way to create a formula that doesn't change week numbers to dates so I can still use week numbers based on what week date it is? Might be a stretch but if not then I'm open to any suggestions.

  • Is this what you are looking for?


    =IFERROR(INDEX(Sheet2!$B$2:$AZ$8000,MATCH($A2,Sheet2!$A$2:$A$8000,0),MATCH(WEEKNUM(B$1),Sheet2!$B$1:$AZ$1,0)),"")

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • The formula works but it misplaces some values later on. It does not take into account that not all of the weeks are on sheet2, so it goes in order on sheet1 instead of placing the right amount in the correct week. Here is a screenshot of my actual workbook. On the line with 650, 1625, 2300, and 4750, everything is correct but the 4750 is being placed in december, since on both sheet1 and sheet2 it is counting the fact that it is just 5 cells over and places it there. Hope that makes sense.


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

  • Your sample doesn't match the data you posted so I can't verify.


    Also, my formula looks at the weeknumber in row 1 of sheet1 and matches it to the number in row 1 of sheet 2.


    When I put a number in column W of the sheet you original supplied (which corresponds to 7 in row 1), then that number is transferred to the Feb-11 column of sheet 1 since it coincides with weeknumber 7 of the year.


    If it still doesn't work as expected post the actual example of it going wrong.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

Participate now!

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