Index / Match / Min

LanceLance

Board Regular
Joined
Feb 4, 2009
Messages
60
Morning.
What I am looking to do/ need help with is:
Looking at blocks of data (top table) and trying to get all data from a certain date entered in to “Review Date” to appear in Results table
Lookup date in “Review Date” and in row 1 of results table display date and earliest time based on date.
Problem is lets say for 9th October 2012 how do I get dates and times in order (earliest to latest) in the results table?
Know once I get dates and times in the results table I can use a =Index/Match function to gather the data from Colum A,B,C,D,E,F and G
but just cant get dates in order?
Maybe an Index/Match/ Min formula?

Data
DateTimeABC D EF G
06-Oct-1202:42152757.76864.5958.2957.8716.3313.49
06-Oct-129.59178048.6865.2557.8257.7316.613.19
06-Oct-1211:20179691.94866.0157.7757.7816.613.23
06-Oct-1215:30164107.18865.358.6658.4716.7213.42
08-Oct-1208:38166292.06859.8156.8456.7916.4813.72
08-Oct-1211.07160592.04859.3156.856.7516.4413.7
08-Oct-1215:30162171.2859.1156.5456.5116.413.46
09-Oct-1202:42161497.04858.3256.3656.2916.4113.37
09-Oct-129.59161705.62837.9256.5456.5316.5513.38
09-Oct-1211:20162269.61857.9956.5956.5816.5413.36
09-Oct-1215:30159384.2858.2156.4656.4216.413.36
09-Oct-1223:17157483.99858.3556.6556.616.3913.34
18-Oct-129.59158971.89857.5155.9755.9416.513.59
09-Nov-1223:171000000
03-Apr-1421:22170900.785960.0760.116.7213.22
04-Apr-1411:20194072.88906.6559.359.817.2512.99
05-Apr-1402:42197737.14906.9159.3159.3317.213.14
05-Apr-149.59198166.31910.0359.1559.1817.2213
11-Apr-1411.07161806.5855.51258.9558.7416.9813.17
11-Apr-1415:30165163.83856.2759.0358.7816.9713.18
Review Date09/10/2012
Results
DateTimeABC D EF G
09/10/2012

<colgroup><col style="width: 64pt; mso-width-source: userset; mso-width-alt: 3108;" width="85"> <col style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;" width="75"> <col style="width: 48pt;" span="7" width="64"> <tbody>
</tbody>


Thanks Lance
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

StefanVL

Board Regular
Joined
Mar 7, 2013
Messages
146
I'd add an extra column before date, in the result table called idx,

in this column put:

=IFERROR(SMALL(IF($A$2:$A$21=$M$2;ROW($A$2:$A$21)-ROW($A$2)+1;"");ROWS($K$6:K6));"")

ENTER WITH CTRL + SHIFT + ENTER

then in the first cell under date put:

=IF($K6="";"";INDEX($A$2:$I$21;$L6;Match(L$5;$A$1:$I$1;0)))

K6 is the column where i've put the top formula, L5 is the first header of the result table.

See end results in table below

Results
idxDateTimeABCDEFG
8,0009-Oct-120,1125161497.04858.3256.3656.2916.4113.37
9,0009-Oct-129.59161705.62837.9256.5456.5316.5513.38
10,0009-Oct-120,4722222162269.61857.9956.5956.5816.5413.36
11,0009-Oct-120,6458333159384.2858.2156.4656.4216.413.36
12,0009-Oct-120,9701389157483.99858.3556.6556.616.3913.34

<tbody>
</tbody>
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,576
Messages
5,596,992
Members
414,115
Latest member
SFUser

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
Top