Formula to pick stores with lowest miles?

[bbrimberry]

Hello,

I am hopeful someone will be able to help me with the problem outlined below.

I have 2 tables. One table has sales reps and the number of hours in a week they work.

Rep	Weekly Hours	Capacity How many hours left?
John	38	2
Mary	37	3
Sue	39	1
Bob	40	0

<tbody>
</tbody>

My second table has stores each rep can drive to.

Rep	Store Number	Miles Away
John	18	85
Mary	27	91
Sue	40	45
Bob	34	31
John	5	55
Mary	2	55
Sue	6	99
Bob	32	21
John	1	13
Mary	12	98
Sue	11	58
Bob	45	99
John	11	28
Mary	1	90
Sue	42	49
Bob	42	55
John	4	30
Mary	5	30
Sue	25	63
Bob	15	99
John	22	95
Mary	47	76
Sue	44	56
Bob	48	33
John	50	56
Mary	50	26
Sue	7	64
Bob	23	100
John	14	71
Mary	38	32
Sue	13	10
Bob	37	79

<tbody>
</tbody>

I need to get excel to return the stores that are within each reps capacity of 40 hours.

I would like excel to somehow return the stores with the least number of miles.


if we used rep john as an example john has 2 hours left.
lets also assume that each store is 1 hour of work.
so i would like exel to return the 2 rows below as the answer

Rep	Store Number	Miles Away
John	1	13
John	11	28

<tbody>
</tbody>

thanks for any help provided.

 

[Aladin Akyurek]

The stop rule is not clear. That is, what is the reason/condition for stopping after two records for john?

 

[bbrimberry]

thanks for the reply. i would like to stop when the hours = 40. each store in the table is 1 hour. so for john i want to return the closest 2 stores

 

[bbrimberry]

i also realized that i may not have been clear. lets assume each store in the store table is 1 hour.

 

[sheetspread]

A single formula and pivot table can do this:

Excel 2010
	A	B	C	D	E	F	G	H	I
1	Rep	Weekly Hours	Capacity How many hours left?			Rep	Store Number	Miles Away	Index
2	John	38	2			John	18	85	0
3	Mary	37	3			Mary	27	91	0
4	Sue	39	1			Sue	40	45	0
5	Bob	40	0			Bob	34	31	0
6						John	5	55	0
7	Rep	Index	Store Number	Miles Away		Mary	2	55	0
8	John	2	1	13		Sue	6	99	0
9	John	5	11	28		Bob	32	21	0
10	Mary	4	50	26		John	1	13	2
11	Mary	6	5	30		Mary	12	98	0
12	Mary	9	38	32		Sue	11	58	0
13	Sue	1	13	10		Bob	45	99	0
14						John	11	28	5
15						Mary	1	90	0
16						Sue	42	49	0
17						Bob	42	55	0
18						John	4	30	0
19						Mary	5	30	6
20						Sue	25	63	0
21						Bob	15	99	0
22						John	22	95	0
23						Mary	47	76	0
24						Sue	44	56	0
25						Bob	48	33	0
26						John	50	56	0
27						Mary	50	26	4
28						Sue	7	64	0
29						Bob	23	100	0
30						John	14	71	0
31						Mary	38	32	9
32						Sue	13	10	1
33						Bob	37	79	0
Sheet7 (2)

Cell Formulas
Range	Formula
I2	{=IFERROR(IF(H2<=SMALL(IF($F$2:$F$33=F2,$H$2:$H$33),VLOOKUP(F2,$A$2:$C$5,3,0)),RANK(H2,$H$2:$H$33,1),0),0)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[bbrimberry]

this is what i needed!! thank you very much!!!