Excel index match, lookup value if date matches or week number of that date matches?

Question

I am using the following index match array formula on my home sheet:

=IFERROR(INDEX(Data!B:B,MATCH(1,(Home!H10=Data!C:C)*(Home!I10=Data!D:D)*(Home!J10=Data!E:E),0)),"")

This looks up my PO number from column B, on data sheet, where the values on home sheet match.

Data: 
Column B         Column C      Column D     Column E         COlumn F (Week number)
12345678910      123           5555         21/12/2016       51

Home: 
Column H      Column I     Column J         Column K         Column J
123           5555         21/12/2016                        12345678910

This gives the PO in column J as a result. This works fine.

Here's my problem:

I want the user to be able to enter either a specific date, i.e. 21/12/2016 in column E and have this find a match. But, sometimes, the user will not know the specific date, and will only have a week number.

Therefore, if the user enters in the week number for 21/12/2016, then my index match formula should still find a match regardless.

The second problem i'm having is unless i format column J on home sheet as date, then my date does not format properly and i get '42725'.

So this has to be set as date in order for the index match to find a result.

But then if the user wants to enter the week number in column J (with a date format), i.e. 51 - then it displays '20/02/1900'. And likewise, my index match will not return a result.

This looks like an excel formula question, not a VBA question. — C8H10N4O2
@ScottCraner thanks i've tried that but this doesn't solve my issue around the formatting in column J. If i type in a week number into a date formatted cell it doesn't display as a week number. Is there a format workaround i could use for this column? — Princess.Bell

Scott Craner Scott Craner · Accepted Answer · 2016-12-22T17:24:29

Instaed of ..*(Home!J10=Data!E:E)...

Use this in its place:

.....*(IF(Home!J10<55,Home!J10,WEEKNUM(Home!J10))=Data!F:F)...

Leave the column Formatted as General and each cell will then change to a date if a date is entered and a number if a week number is entered.

You may need to go through and reenter some of the entries to get them back to what they should be the first time, but any new entries will shift on their own.

Excel index match, lookup value if date matches or week number of that date matches?

1 Answers