Forum

Notifications
Clear all

INDEX and MATCH values between dates

6 Posts
2 Users
0 Reactions
74 Views
(@artos90)
Posts: 5
Active Member
Topic starter
 

I am using this formula =INDEX('OEE Report'!$C:$C,MATCH($A4,'OEE Report'!$B:$B,0)) to see which part numbers are running on a specific molder. I want to ensure that I am seeing the most recent part number based on yesterday's date. 

The formula is in the Master Downtime Analysis 2018 worksheet in Column B - Part Numbers. I was trying to insert an IF statement to only show part numbers on each molder from yesterday's date in Cells D1 and D2. Not having much success, so any help would really be appreciated. I have tried several iterations of the formula with an IF Statement but now I'm lost. 

=INDEX(('OEE Report'!$C:$C,MATCH(IF($A4="","",$A4,'OEE Report'!$A:$A,">=D$1,'OEE Report'!$A:$A,<=$D$2,($A4,'OEE Report'!$B:$B,0))

 

Any help would be truly appreciated. 

 
Posted : 16/04/2018 1:43 pm
(@sunnykow)
Posts: 1417
Noble Member
 

Hi Chris

What is it that is not working? Can you give an example of which cell is not correct and what is the expected value?

I noted that you are using TODAY()-1 to get yesterday's date.

If you open your file today (16/04/2018), yesterday's date will be 15/04/2018.

Your formula win not be able to extract any data as your data's last date is only up till 13/04/2018.

Could this be the problem?

Sunny

 
Posted : 16/04/2018 10:51 pm
(@artos90)
Posts: 5
Active Member
Topic starter
 

HI SunnyKow,

I appreciate your valuable feedback. Yes, the OEE report table is updated on a daily basis with data from yesterday production runs, so the formulae work as soon as the data is entered and I refresh the pivot tables for yesterday, past 7 days and overall totals.

I was just trying to amend the part number formula to make sure that molders running specific part numbers were being updated if any changes occurred during the previous 24 hours when yesterdays data is added to the OEE report worksheet. 

 
Posted : 17/04/2018 3:33 pm
(@sunnykow)
Posts: 1417
Noble Member
 

Hi Chris

Give this a try. The formula is in column F. It will extract the part number based on the Molder (column A) and yesterday's date (cell D1)

It is an array formula so you will need to press CTRL+SHIFT+ENTER instead of a normal ENTER to complete the entry.

The formulas are currently showing errors but once you have the correct data, it will show the part number.

Hope this helps.

Sunny

 
Posted : 17/04/2018 11:20 pm
(@artos90)
Posts: 5
Active Member
Topic starter
 

SunnyKow, 

Thank you so much for your help. The array formula works perfectly. I am able to now see the latest product number for each molder. I really appreciate you taking the time to help me with this issue. 

Chris

 
Posted : 18/04/2018 2:12 pm
(@sunnykow)
Posts: 1417
Noble Member
 

Hi Chris

Thanks for your feedback.

Glad to know it is working for you.

Cheers

Sunny

 
Posted : 18/04/2018 7:02 pm
Share: