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!

	A	B	C
1	John	11/1/17	10
2	John	11/2/17	15
3	Kate	11/1/17	14
4	John	11/3/17	18
5	Kate	11/2/17	20
6	Jim	11/1/17	25
7	Kate	11/3/17	30
8	Kate	11/4/17	40
9	Jim	11/1/17	20
10	John	11/4/17	60

<tbody>
</tbody>

 

[Eric W]

Try:

	A	B
1	John	31
2	Kate	30
3	Jim	#NUM!

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Array Formulas Cell Formula B1 {=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)))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

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!