Finding the right vlookup/index formula in excel to match weeks and dates

1
votes

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). Finding a way to change the formula to match the week number with the date might be a possible solution? Hope this makes sense! The workbook is saved daily with a new date.

google sheet link

Sheet1

Sheet2

2
excelexcel-formulamatchvlookup
COLUMN() will return the column number of the cell the formula is in. I don't think that's what you want. - robartsd
Instead of IF(ISNA(expression1),"",expression1), try IFERROR(expression1,"") unless you want to see errors that are not NA. - robartsd
An image may help explain. - Solar Mike
a) Post a redacted copy of your worksheet to google sheets and create a viewable link you can add to your question through an edit. b) Edit your question to include enough sample data in a code block that we can duplicate the problem. c) Edit your question to include an image (or imgur link to image) of your redacted worksheet. While I find it rude that someone seeking assistance would expect everyone responding to their inquiry to retype the data from an image, apparently others don't mind as much. - user4039065
That formula would have to be put in column E to BD. The COLUMN()-4 is taking the numerical column index and subtracting 4 so column E would return the value from column Sheet2!A:A. - user4039065

2 Answers

0
votes

Try this in Summary!B2 then fill both right and down.

'excel formula
=iferror(hlookup(weeknum(B$1, 2), Sheet2!$B$1:$AZ999, match($A2, Sheet1!$A:$A, 0), false), "")
'google-sheets formula
=iferror(hlookup(weeknum(B$1, 2), Sheet2!$B$1:$AZ, match($A2, Sheet1!$A:$A, 0), false), "")

The accuracy of the HLOOKUP will depend on whether your WEEKNUM's match the Excel default for WEEKNUM. I noticed your dates are the Monday starting the week so I've use WEEKNUM(<date>, 2). If your weeknum's are offset from excel defaults (implied by the COLUMN()-4 parameter) then this will have to be adjusted.

Shared worksheet

enter image description here

0
votes

Why don't try to use :

  1. Use weeknum() for week number of the year. instead of manually setting it. eg Let A1 = the date, =WEEKNUM(A1)

  2. For checking of empty cell , use iferror() or if() to check/zero-fill to check for blank cell. eg. =IF(A1="",WEEKNUM(A1),"")

  3. Use index-match instead of vlookup. It may be used to look for match both vertically and horizontally.. ref -> http://www.mbaexcel.com/excel/how-to-use-index-match-match/
    eg > Let A2 = a, B2 = b, C2 = c, A3 = 30, B3 = 40, C3 = 50 . using A5 as text(a/b/c) input, =INDEX(A3:C3,1,MATCH(A5,A2:C2,0))

eg2 > Let A10 = a, A11 = b, A12 = c, B10 = 30, B11 = 40, B12 = 50 , =INDEX(B10:B12,MATCH(A5,A10:A12,0))

Hope that helps. (:

+-------[ Edit ]---------+

Thanks for the shared files & screenshoot. Congratulations on finding the solution =IFERROR(INDEX(Sheet2!$B$2:$AZ$8000,MATCH($A3,Sheet2!$A$2:$A$8000,0),MATCH(WEEKNUM(B$1),Sheet2!$B$1:$AZ$1,0)),"") on your own. It is indeed a very good solution. Keep it up. (: