Excel index match: If date week number matches date?

Question

I have 2 excel spreadsheets, sheet 1 and sheet 2:

Sheet 2

Col F (Date)   Col G (Item No)  Col H (Supplier code)   Col I (PO Number)
21/12/2016         12345                 123            PO1111
18/12/2016         5555                  2468           PO2222
                                         123            po982

Sheet 1

Column F (Week No or date)    Column G (Item No)    Col H (Supplier   Col I (Formula) Code)   
51                            12345                 123
18/12/2016                    5555                  2468

In Column I on sheet 1, i want to add my index match formula:

=INDEX(Sheet2!D1, MATCH(F4&G4&H4,Sheet2!A:A&Sheet2!B:B&Sheet2!C:C, 0))

This should return the PO number from column I on sheet 2 where column F, G and H match.

This works fine. And from the example above, this formula would return a result for the last row:

18/12/2016                    5555               2468         PO2222

You might have noticed that the first row in column (F) on sheet 1 has a week number 50 - yet there is a date of 21/12/2016 in column F on sheet 2.

By rights this would not give a matching result.

However, Column F on sheet 2 will either have a week number or a specific date in it. And if this is the case, i want my formula to check for both a matching date or where a matching date is not found, check if the date falls within that week number? So for instance 21/12/2016 would fall into week 51 (week beginning monday).

Is there a way i can amend my index match formula accordingly to get this result?

Thanks in advance

Can you use a helper column. For eg. in sheet 2 COl J = WEEKNUM(F2) — nightcrawler23

nightcrawler23 nightcrawler23 · Accepted Answer · 2016-12-21T01:56:15

I added a helper column in Sheet 2 col J to get the week number for the dates in Col F. In sheet1 I used this formula for col I

=INDEX(Sheet2!I:I,SUMPRODUCT(IF(YEAR(F3)<2016,(Sheet2!J:J=F3),(Sheet2!F:F=F3))*(Sheet2!G:G=G3)*(Sheet2!H:H=H3)*ROW(Sheet2!I:I)))

The below part of the formula

IF(YEAR(F3)<2016,(Sheet2!J:J=F3),(Sheet2!F:F=F3))

checks if the year for date is less than 2016. If week number was entered, Year() will return a value of 1900. if true it compares the week number to the col K in sheet 2 else it compares the dates in col F for both sheets.

Excel index match: If date week number matches date?

1 Answers