index match or offset?

[bgamach1]

I am trying to type in a name in column D, and have the data from column B (for that name), generate in ascending order in column E. Any ideas? Is it an index match formula?

	A	B	C	D	E	F
1	Matthew	1/1/18	37	Mark	1/1/18	45
2	Mark	1/1/18	45	Mark	1/5/18	41
3	Luke	1/1/18	52	Mark	1/12/18	39
4	John	1/1/18	55
5	Paul	1/1/18	58
6	George	1/1/18	42
7	Rich	1/1/18	48
8	Tom	1/1/18	51
9	Matthew	1/5/18	39
10	Paul	1/5/18	60
11	Mark	1/12/18	39
12	John	1/12/18	47
13	Luke	1/12/18	39
14	Mark	1/5/18	41
15	Tom	1/5/18	51
16

<tbody>
</tbody>

 

[steve the fish]

In E1:

=INDEX($B$1:$B$15,MATCH(1,($A$1:$A$15=D1)*($C$1:$C$15=F1),0))

In F1:

=LARGE(IF($A$1:$A$15=D1,$C$1:$C$15),ROWS($A$1:A1))

Both need entering CTRL-SHIFT-ENTER not just ENTER.

 

[Ron Coderre]

I'm assuming that you want to enter a name in D1 and have all of the activity for that person list in ascending order by date.
If that's true, try this approach (using your posted example):
D1: a name....Mark

D2: =IF((COUNTIF($D$1:$D1,$D$1)+1)<=COUNTIF($A$1:$A$15,$D$1),$D$1,"")

Copy that formula down through D15

E1: =IF(D1<>"",SMALL(INDEX(($A$1:$A$15=$D$1)*$B$1:$B$15,0),ROWS($1:1)+COUNTIF($A$1:$A$15,"<>"&$D$1)),"")

F1: =IF(D1<>"",SUMIFS($C$1:$C$15,$A$1:$A$15,$D$1,$B$1:$B$15,E1),"")

Copy those formulas down through Row 15

All formulas are regular...not Array Formulas.

Is that something you can work with?

 

[bgamach1]

Yes. The =If formulas worked.

Let's say I want the dates for column E to go from most recent to least recent?

I tried adjusting it to this, but it didn't work.

=IF(D1<>"",LARGE(INDEX(($A$1:$A$15=$D$1)*$B$1:$B$15,0),ROWS($1:1)+COUNTIF($A$1:$A$15,"<>"&$D$1)),"")

 

[bgamach1]

When I change the formula in Cell E1 to =IF(D1<>"",LARGE(INDEX(($A$1:$A$15=$D$1)*$B$1:$B$15,0),ROWS($1:1)+COUNTIF($A$1:$A$15,"<>"&$D$1)),""), the rows in E1, E2, and E3 return 1/0/1900. Is there a way to change it to read the dates in column B from most recent to least recent?

 

[Peter_SSs]

See if this is any use for the original question.

Name entered in D1, all the shown formulas copied down.
Assumption is that each name/date combination only occurs once in the table.

Excel Workbook
	A	B	C	D	E	F
1	Matthew	01-Jan-18	37	Mark	01-Jan-18	45
2	Mark	01-Jan-18	45	Mark	05-Jan-18	41
3	Luke	01-Jan-18	52	Mark	12-Jan-18	39
4	John	01-Jan-18	55
5	Paul	01-Jan-18	58
6	George	01-Jan-18	42
7	Rich	01-Jan-18	48
8	Tom	01-Jan-18	51
9	Matthew	05-Jan-18	39
10	Paul	05-Jan-18	60
11	Mark	12-Jan-18	39
12	John	12-Jan-18	47
13	Luke	12-Jan-18	39
14	Mark	05-Jan-18	41
15	Tom	05-Jan-18	51
List (2)

Notes:
1. If you don't have the MAXIFS function (introduced in Excel 2016), or in any case, use the F1 formula suggested by Ron.
2. To have the results listed in the opposite date order, simply change the first argument of the AGGREGATE function in cell E1 from 15 to 14 & copy down. All other formulas remain the same.
@steve the fish
Your formula for column E (dates) will fail if the person noted in column D has the same value in column C for more than one date. For example, change the second last value in the col C sample data to 45 and note your results.