title: Excel index match, lookup value if date matches or week number of that date matches?
body: <p>I am using the following index match array formula on my home sheet:</p>
  
  <pre><code>=IFERROR(INDEX(Data!B:B,MATCH(1,(Home!H10=Data!C:C)*(Home!I10=Data!D:D)*(Home!J10=Data!E:E),0)),"")
  </code></pre>
  
  <p>This looks up my PO number from column B, on data sheet, where the values on home sheet match.</p>
  
  <pre><code>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      
  </code></pre>
  
  <p>This gives the PO in column J as a result. This works fine.</p>
  
  <p>Here's my problem:</p>
  
  <p>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. </p>
  
  <p>Therefore, if the user enters in the week number for 21/12/2016, then my index match formula should still find a match regardless.</p>
  
  <p>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'.</p>
  
  <p>So this has to be set as date in order for the index match to find a result.</p>
  
  <p>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. </p>