VBA - WorksheetFunction.Small with conditions??

[zico8]

Hi,

Column A consists names, and Column B consists numbers.

How can I find 3rd lowest value from column B, where column A contains "John" ?

 

[CalcSux78]

Excel 2010

	A	B	C	D	E	F
1	Name	Number		Name	Smallest	Result
2	Mark	112		John	3	153
3	John	951
4	Luke	422
5	John	18
6	John	847
7	Mark	584
8	Matthew	415
9	Matthew	393
10	Mark	74
11	Mark	543
12	Mark	351
13	Luke	299
14	Luke	719
15	John	6
16	Matthew	923
17	Mark	349
18	Mark	340
19	Matthew	537
20	John	426
21	John	153

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

Sheet1​

Array Formulas Cell Formula F2 {=SMALL(IF(D2=A:A,B:B,""),E2)} <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>

 

[zico8]

Thanks a lot CalcSux78,

but I need solution for VBA