Index match ?? Not sure what functions to use

sharv103

New Member
Joined
Feb 13, 2013
Messages
8
Hello,

I have 2 worksheets one with summary data and other with full data. I need to return the earliest date in the full data set based on the following conditions:
Cell in summary data set = A
lookup full data set in 2nd worksheet that has the same value various times in Col A
then lookup the range that have the value A in Col A And check if col B is empty
then lookup range in Col C of the values that = tat of A and B and return earliest date.

Can anyone help?

Thanks
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Could you use some sample data and show us what the expected result would be
 
Upvote 0
HI Here is a sample data set from the full data set. And if Col A in the summary set is = "A" in the Task Col then I want to return the earliest date in the deadline col, if the the completed col is empty in the range that is = "A" in Task Col.


My results should be for:
Task A - 15/10/2014
Task B - 30/09/2014
Task C - 15/11/2014
Task
Action
Description
Responsibility
Deadline
Completed
Notes
A
1
Requirements to be specified
30/09/2014
30/09/2014
A
2
Initial meeting to be arranged
15/10/2014
A
3
Data to be put together and sent to Ian
15/12/2014
A
4
Data to be edited into the required format (if necessary)
31/12/2014
A
5
Data to be uploaded to the portal
31/01/2015
B
1
Requirements to be specified
30/09/2014
B
2
Initial meeting to be arranged
15/10/2014
15/10/2014
B
3
Standard definitions to be established
15/11/2014
B
4
Data to be put together
15/12/2014
B
5
Data to be edited into the required format (if necessary)
31/12/2014
B
6
Data to be uploaded to the portal
31/01/2015
C
1
Contact made
15/10/2014
15/10/2014
C
2
Requirements to be specified
15/11/2014
C
3
data put together
31/01/2015
C
4
data edit
15/02/2015
C
5
Data to be uploaded to the portal
28/02/2015
D
1
Requirements to be specified
31/12/2014
D
2
Data to be edited into the required format (if necessary)
31/01/2015
D
3
Data to be uploaded to the portal
15/02/2015
E
1
Requirements to be specified
30/09/2014
E
2
Initial meeting to be arranged
15/10/2014

<TBODY>
</TBODY>
 
Last edited:
Upvote 0
Like this


Excel 2010
ABCDEF
1TaskActionDescriptionResponsibilityDeadlineCompleted
2A1Requirements to be specified30/09/201430/09/2014
3A2Initial meeting to be arranged15/10/2014
4A3Data to be put together and sent to Ian15/12/2014
5A4Data to be edited into the required format (if necessary)31/12/2014
6A5Data to be uploaded to the portal31/01/2015
7B1Requirements to be specified30/09/2014
8B2Initial meeting to be arranged15/10/201415/10/2014
9B3Standard definitions to be established15/11/2014
10B4Data to be put together15/12/2014
11B5Data to be edited into the required format (if necessary)31/12/2014
12B6Data to be uploaded to the portal31/01/2015
13C1Contact made15/10/201415/10/2014
14C2Requirements to be specified15/11/2014
15C3data put together31/01/2015
16C4data edit15/02/2015
17C5Data to be uploaded to the portal28/02/2015
18D1Requirements to be specified31/12/2014
19D2Data to be edited into the required format (if necessary)31/01/2015
20D3Data to be uploaded to the portal15/02/2015
21E1Requirements to be specified30/09/2014
22E2Initial meeting to be arranged15/10/2014
23
24A15-10-2014
25B30-09-2014
26C15-11-2014
Sheet6
Cell Formulas
RangeFormula
E24{=MIN(IF($A$2:$A$22=D24,IF($F$2:$F$22="",$E$2:$E$22)))}
E25{=MIN(IF($A$2:$A$22=D25,IF($F$2:$F$22="",$E$2:$E$22)))}
E26{=MIN(IF($A$2:$A$22=D26,IF($F$2:$F$22="",$E$2:$E$22)))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
HiI yes, however the lookup value is in a different sheet in Col A

Summary Sheet example:

Task Date due
A 15/10/2014
B 30/09/2014
C 15/11/2014

So I would need to incorporate that into the lookup
 
Last edited:
Upvote 0
So for example, where i have D24, change to Summary!A1 , if "A" is on a sheet called Summary and in cell A1

i.e like {=MIN(IF($A$2:$A$22=Summary!A1,IF($F$2:$F$22="",$E$2:$E$22)))}
 
Upvote 0

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top