Using Index and Match For Multiple Criteria Mismatch Data Type

[shelby55]

Hi

I am using Excel 2003.

How do I combine an index and match formula for arrays where the data fields are not the same i.e. one is text and one is date.

The formula of:

=INDEX(Ttl_Sick_MOS,MATCH(A14,MyDates,0))

works but it is finding the first occurrence of the month and not also matching to department.

It looks to me like filtering the raw data sheet doesn't matter for selection of my data because my indexes etc. aren't looking at only the filtered data, it is looking at the entire worksheet anyway thus the need to match both.

Any assistance greatly appreciated - thanks.

 

[SkipVought]

Hi,

If I am understanding the REASON for your question, it is totally not relevant to performing the filtering for you chart.

Please explain what data you need to return.

Post a relevant example, including what rows are visible and not.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi

Raw data shows as:
[tt]Department MonthEnding Sick_Hrs OT_Hrs Total_Hrs
Medicine 9/30/2010 124.29 193.92 1047.83
Medicine 8/31/2010 128.57 152.35 984.79
Surgery 9/30/2010 6.21 107.36 757.53
Surgery 8/31/2010 6.86 233.34 823.55[/tt]

the above is without filters. If I filter on Surgery and MonthEnding 9/30/2010 and use the code:

=INDEX(Ttl_Sick_MOS,MATCH(A14,MyDates,0))

then it returns 124.29 because that is the first match to 9/30/2010 no matter that the filter "hid" that value but I want 6.21 because I've selected Surgery.

Thanks.

 

[SkipVought]

[tt]
=sumproduct((Department=E4)*(MyDates<=F4)*(MyDates>=G4)*(Ttl_Skck_MOS))
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

Thanks. Sorry but what are you using for E4, F4 and G4?

 

[shelby55]

Hi Skip

I figured it out, thanks. The problem now is just that I can't figure out how to get the 6 timeframes I want for the graph...my current formula is pulling incorrect data. For the months where they are being pulled correctly like in the last spot, it is working as I wanted so I'm sure it will work once I figure the other part out.

Thanks again for your assistance.

 

[SkipVought]

you must code the lower limit in G4 to handle ANY case. Then THOSE values are the one you can use for all sorts of formulas like the sumproduct I previously posted.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

As per my post on the other thread, I'm not understanding. Where is G4 located...on the finance_raw worksheet, graph_data worksheet?

As per my other post, what are you assuming E4, F4 and G4 are for the formula you provided? Thanks.

 

[SkipVought]

Where is G4 located...

This sheet is where you are SELECTING the Dept (E4) & Date upper criteria (F4). So what better place to calculate the LOWER date criteria than here (G4)!!!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi

I was hoping not to do that because that is where the graphs will be shown and the user selects the department and month_ending. I don't want the graphs to grow beyond 6 months of data so don't want the user to be able to select that.

That is why I keep talking about this graph_data worksheet and limiting it that way. I'm just trying to figure out how to get at the 6 months (or the months between min and max) and then I can use the sumproduct.

 

[SkipVought]

I don't want the graphs to grow beyond 6 months of data so don't want the user to be able to select that.

ITS A CALCULATION!!!!!!!

The user is ONLY selecting the UPPER DATE (F4)

ITS A CALCULATION!!!!!

ITS A CALCULATION!!!!!

ITS A CALCULATION!!!!!



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

HI

Okay, I put the formula where you suggested (even though I had it on the graph_data worksheet). So using the formula above for sumproduct, how is this going to give me each specific 6 months of data? It only gives me the total for the 6 months (or however many months there are).

I used:

=SUMPRODUCT((MyDepartments=F4)*(MyDates<=F5)*(MyDates>=F6)*(PC_Sick_MOS))

Note that F4 = department selected, F5 = maximum date and F6 = minimum date from the original worksheet.

What am I not getting here??????

 

[SkipVought]

how is this going to give me each specific 6 months of data?

It does not. It is an AGGREGATION formula.

What do you want to do with "each specific 6 months of data?"

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

In my other thread I indicated that I want to plot each month's data on a graph. The problem is that the data isn't static and I want to limit to 6 months of data. So that is why I was trying to figure out the months between min and max to then use those values to use index or something to get the data from the raw_data worksheet based on the department selected and month.

 

[SkipVought]

trying to figure out the months between min and max to then use those values to use index or something to get the data from the raw_data worksheet based on the department selected and month.

And what do you expect to do with this data?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi

As per the post above, I want to graph the 6 data points.

Thanks.

 

[SkipVought]

Are those NOT the 6 VISIBLE data points in the filtered table? THAT is the objective here!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi

Not sure what 6 visible points you're talking about because that isn't what I was getting. As per my other post I now have the filter working so hopefully I can just graph from the raw_worksheet.

Thanks.

 

[SkipVought]

As posted in the other thread, your code is flawed, as only ONE criteria is ever applied. You NEVER assign TWO criterias, which is essential to achieving your stated requirement!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Thanks....can you please advise how I do that? I've been trying to find answers on my own and haven't been able to do so.