Multiple Criteria lookup to extract data from a dump - INDEX MATCH????

[Rossovich]

I am attempting to extract data from a dump onto a blank sheet in a workbook, I would like to create a formula that will look at 3 separate reference items, so that it will pull the correct data.
i.e.
it needs to look at date, time and skill.
I have attached my sheet. the formula would need to go in the green box.
It needs to look at the time (Column A), the date which Cell A2 and the skill which is cell CU1
It needs to look at the tab Week 1 and grab the data if there is any, If it does not find any data, I need it to leave the cell blank.

I looked at VLOOKUP, if statements and Index match. I am not overly familiar with index match, but by the looks of it; it may be my solution???

Any help would be greatly appreciated.

[6StringJazzer]

INDEX and MATCH allow you to look up a single value. What you need to do is get the sum of a whole bunch of values. This is the formula you want for the green cell, and you can copy down:

Please Login or Register  to view this content.

However, for your data, the result is 0 for all times--there are no calls with skill 576 at all.

[Rossovich]

Hi Jeff, thank you for your response. I am not looking for a SUM or a COUNT. my example is for a small portion of what is needed. What that green cell needs to look at is three separate references and bring back the result. That is it. The data that is normally on the Week 1 tab is a full week of data, I could not put it on the example as this site has a limit on file size. When I put all data in the file is almost 6MB.
Hope that clears things up

[loginjmor]

Hi -

I think if you just substitute SUMIFS for COUNTIFS in Jeff's formula, you will get the result you want since it appears those 3 criteria result in one single, unique match. So it will just sum the one occurrence and bring it back from the other sheet.

[Rossovich]

thank you for the help guys, but figured out my answer:
=INDEX(Week1!$A:$Q,MATCH(1,(Week1!$C:$C='576'!$A5)*(Week1!$F:$F='576'!$CU$1)*(Week1!$B:$B='576'!$A$2),0),14) then a CTRL + SHIFT+ ENTER
works perfectly