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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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
[TABLE="width: 863"]
<TBODY>[TR]
[TD]Task
[/TD]
[TD]Action
[/TD]
[TD]Description
[/TD]
[TD]Responsibility
[/TD]
[TD]Deadline
[/TD]
[TD]Completed
[/TD]
[TD]Notes
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]1
[/TD]
[TD]Requirements to be specified
[/TD]
[TD][/TD]
[TD]30/09/2014
[/TD]
[TD]30/09/2014
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]2
[/TD]
[TD]Initial meeting to be arranged
[/TD]
[TD][/TD]
[TD]15/10/2014
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]3
[/TD]
[TD]Data to be put together and sent to Ian
[/TD]
[TD][/TD]
[TD]15/12/2014
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]4
[/TD]
[TD]Data to be edited into the required format (if necessary)
[/TD]
[TD][/TD]
[TD]31/12/2014
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]5
[/TD]
[TD]Data to be uploaded to the portal
[/TD]
[TD][/TD]
[TD]31/01/2015
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]1
[/TD]
[TD]Requirements to be specified
[/TD]
[TD][/TD]
[TD]30/09/2014
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]2
[/TD]
[TD]Initial meeting to be arranged
[/TD]
[TD][/TD]
[TD]15/10/2014
[/TD]
[TD]15/10/2014[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]3
[/TD]
[TD]Standard definitions to be established
[/TD]
[TD][/TD]
[TD]15/11/2014
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]4
[/TD]
[TD]Data to be put together
[/TD]
[TD][/TD]
[TD]15/12/2014
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]5
[/TD]
[TD]Data to be edited into the required format (if necessary)
[/TD]
[TD][/TD]
[TD]31/12/2014
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]6
[/TD]
[TD]Data to be uploaded to the portal
[/TD]
[TD][/TD]
[TD]31/01/2015
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]1
[/TD]
[TD]Contact made
[/TD]
[TD][/TD]
[TD]15/10/2014
[/TD]
[TD]15/10/2014[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]2
[/TD]
[TD]Requirements to be specified
[/TD]
[TD][/TD]
[TD]15/11/2014
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]3
[/TD]
[TD]data put together
[/TD]
[TD][/TD]
[TD]31/01/2015
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]4
[/TD]
[TD]data edit
[/TD]
[TD][/TD]
[TD]15/02/2015
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]5
[/TD]
[TD]Data to be uploaded to the portal
[/TD]
[TD][/TD]
[TD]28/02/2015
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD]1
[/TD]
[TD]Requirements to be specified
[/TD]
[TD][/TD]
[TD]31/12/2014
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD]2
[/TD]
[TD]Data to be edited into the required format (if necessary)
[/TD]
[TD][/TD]
[TD]31/01/2015
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD]3
[/TD]
[TD]Data to be uploaded to the portal
[/TD]
[TD][/TD]
[TD]15/02/2015
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]E
[/TD]
[TD]1
[/TD]
[TD]Requirements to be specified
[/TD]
[TD][/TD]
[TD]30/09/2014
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]E
[/TD]
[TD]2
[/TD]
[TD]Initial meeting to be arranged
[/TD]
[TD][/TD]
[TD]15/10/2014
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]
 
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,223,195
Messages
6,170,661
Members
452,345
Latest member
ye4hb0i

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