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