V lookup then average if

[pacerfan07]

I have the following on Sheet1 below. On Sheet2 I just have everyone's Name listed once in column A (John, Kate, and Jim). In column B of Sheet2 I would like to look at the name from column A and see if it matches column A on Sheet1. If it does, then take the 3 most recent occurrences in column B and average the corresponding values in column C. Johns average should be 31.

Thanks!

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]John
[/TD]
[TD]11/1/17
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]John
[/TD]
[TD]11/2/17
[/TD]
[TD]15
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Kate
[/TD]
[TD]11/1/17
[/TD]
[TD]14
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]John
[/TD]
[TD]11/3/17
[/TD]
[TD]18
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Kate
[/TD]
[TD]11/2/17
[/TD]
[TD]20
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Jim
[/TD]
[TD]11/1/17
[/TD]
[TD]25
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]Kate
[/TD]
[TD]11/3/17
[/TD]
[TD]30
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]Kate
[/TD]
[TD]11/4/17
[/TD]
[TD]40
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]Jim
[/TD]
[TD]11/1/17
[/TD]
[TD]20
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]John
[/TD]
[TD]11/4/17
[/TD]
[TD]60
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Try:

	A	B
John
Kate
Jim

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]31[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]30[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]#NUM![/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]{=AVERAGE(IF(Sheet1!$A$1:$A$10=A1,IF(Sheet1!$B$1:$B$10>=LARGE(IF(Sheet1!$A$1:$A$10=A1,Sheet1!$B$1:$B$10),3),Sheet1!$C$1:$C$10)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Note that Jim gets an error because he only has 2 entries. Also note that this could give unexpected results if you have duplicate dates for the same person.

 

[pacerfan07]

That did it. Thanks!

 

[Eric W]

Glad to help!