• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

INDEX & MATCH From Two Worksheets

Chris Turner

New Member
I have a trying to create a formula using the INDEX and MATCH functions to do a daily update of a worksheet.

I want the formula to INDEX the most recent Part Numbers in Column C being run on a specific Molder in Column B in the OEE Report worksheet and to MATCH them with the Molders in Column A of the Master Downtime Analysis 2018 worksheet. I keep getting a #NAME? response.

I have tried several variations of this formula but not success so far:

=INDEX(OEE Report!$C:$C,MATCH($A4,OEE Report!$B:$B,0))

I was just trying to get the part numbers to load into the Master Downtime Analysis 2018 worksheet before I attempted to get the formula to insert the Part Numbers for Yesterdays date in Cells D1:D2

Appreciate any help with this.
 

Attachments

  • forum-sample.xlsm
    64.1 KB · Views: 5
Hi ,

I assume this is the formula you have tried :

=INDEX(OEE Report!$C:$C,MATCH($A4,OEE Report!$B:$B,0))

Here the highlighted part is the worksheet tab name. When ever a worksheet tab name contains a space or any other special character , the entire name has to be enclosed in single quotes. Thus , your formula should be :

=INDEX('OEE Report'!$C:$C,MATCH($A4,'OEE Report'!$B:$B,0))

Narayan
 
Narayan, Thank you for your help. The formula works great. I am now trying to add to the formula to only index and match data on a date range
 
I inserted the formula that Narayan provided and it gave me the part numbers for each molder, however I want to ensure that I am seeing the latest part numbers in Column B in the Master Downtime Analysis 2018 based on yesterday's date in the same worksheet in cells D1 and D2. I have tried amend the formula by inserting an IF statement but have been unable to get it to work. I would truly appreciate any help with this.

=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))
 

Attachments

  • forum-sample.xlsm
    63.4 KB · Views: 2
Attached is a very different approach. It's not finished; Column B (Part Number) needs a formula that updates itself - currently it doesn't. Note that this column can only have part numbers from yesterday, since that's what the data in column D (Yesterday downtime) represents. If there was no part number processed by say Molder 9 yesterday, but you had a part number processed on a previous day by that same molder showing in column B, it would show zero downtime for that part number yesterday which isn't strictly true since that part number wasn't processed at all yesterday! That's why there are some #N/As in there.
It needs 3 pivot tables to grab the information from (on the same sheet at the moment).
The intention is:
Not to have the pivot tables visible (they can be on a hidden sheet)
For them to update themselves and adjust their source range as the data on the OEE Report sheet changes.
They already adjust themselves for Last week and Yesterday (see note below).

Notes:
  • Because your data doesn't include any data from yesterday (I realise it did when you posted it) I've temporarily changed the date filter in the 3rd pivot to be Equals… to the 13th April 2018, just so that something shows in that pivot; you need to change it to simply Yesterday.
  • All molders for all the dates in your sample data worked on just one part number, so I changed some data on the OEE Report sheet so that they worked on different part numbers on different dates. These are highlighted in yellow on that sheet.
  • The list in column A was manually entered (not a formula) and remains so.

This approach uses the built in pivot table data processing which is quite fast. If this is a route you think you might like to take I'll write a few lines of code for the necesssary updating to take place and move the 3 pivot tables to a hidden sheet.

Over to you.
 

Attachments

  • Chandoo38188forum-sample.xlsm
    70.8 KB · Views: 3
Hi ,

In case you still want to go with your formula approach , see the attached file.

When your data is in the form of an Excel table , always make use of structured references.

Narayan
 

Attachments

  • Test version 1.0.xlsm
    64.6 KB · Views: 13
Narayan, Molder 2's downtime for 13th April is a bit low isn't it (cell D4)?
Hi ,

I have changed the formulae as follows :

1. $D$1 and $D$2 will both contain the same date.

2. Column C will contain the downtime for the date in $D$1 / $D$2.

3. Column D will contain the downtime for the date previous to the date in $D$1 / $D$2 ; I am using ($D$1 - 1) for yesterday.

4. Column E makes use of the 7 day period , the dates for which are anyway derived in $E$1 and $E$2.

Narayan
 
Thank you for your help and wonderful feedback.

p45cal - The Master Downtime Analysis 2018 is a table that pulls data from the OEE Report. I have pivot tables pulling and sorting values from this Master Downtime Analysis 2018 table for yesterday, past 7 days and overall totals.

Narayank991 - I am not sure I understand how you are changing the formulae without seeing an example.

Regards
 
p45cal - The Master Downtime Analysis 2018 is a table that pulls data from the OEE Report. I have pivot tables pulling and sorting values from this Master Downtime Analysis 2018 table for yesterday, past 7 days and overall totals.
Gosh, so you have pivot tables based on the Master Downtime Analysis 2018!
So the aim is to produce these pivot tables and charts, and the Master Downtime Analysis 2018 is just a helper or intermediate table to that end?!
If that's the case, I suspect you might not need the intermediate table at all, you can get the same (or very, very similar) pivot tables and charts by pivoting the data in the OEE Report directly.
In the attached I've added some pivot tables and chart beneath each one next to yours which seem to give the same results (notwithstanding that your pivots can't be refreshed because the headers are different - did you alter the source for your pivot without refreshing?).
I can't find your pivot for overall totals, so I've added a pivot and chart for that on Sheet1.
Again, instead of choosing Yesterday, I've temporarily chosen Equals… in the Yesterday pivot date filter.

Or have I missed the point?
 

Attachments

  • Chandoo38188forum-sample_ver2.xlsm
    85.2 KB · Views: 5
Last edited:
p45cal, Thank you for your time and feedback. I incorporated the improvements and my workbook is working much better and now achieving what I wanted it to do
 
Back
Top