Multiple VLOOKUPS and Ranks within a dataset?

[Roghaltz]

Hi All,
Thanks again for your previous help on RANKIf without arrays. I am still working with the same basic model and had a new question. I currently have the first four columns of the table below. I am looking to create SalesmanInOrder which is simply a list of the salesman for a product in order from largest to smallest (i.e. in the example below a3 is the top apple salesman so he goes at the top of the apple list, followed by a1, then a2. I suspect this involves INDEX and/or MATCH, but I can't seem to make it work. As before, all the Apples salesman are together in the dataset, followed by all the Bananas, etc. Each product can have between 6 and 12 rows. I tried a VLOOKUP, but am unsure how to specify the first and last row for the lookup.

Thanks in advance.

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Salesman[/TD]
[TD]Product[/TD]
[TD]PredOutPut[/TD]
[TD]SalesRank[/TD]
[TD]SalesmanInOrder[/TD]
[/TR]
[TR]
[TD]a1[/TD]
[TD]Apples[/TD]
[TD]5.1[/TD]
[TD]2[/TD]
[TD]a3[/TD]
[/TR]
[TR]
[TD]a2[/TD]
[TD]Apples[/TD]
[TD]4.7[/TD]
[TD]3[/TD]
[TD]a1[/TD]
[/TR]
[TR]
[TD]a3[/TD]
[TD]Apples[/TD]
[TD]8.3[/TD]
[TD]1[/TD]
[TD]a2[/TD]
[/TR]
[TR]
[TD] b1[/TD]
[TD]Bananas[/TD]
[TD]7.3[/TD]
[TD]2[/TD]
[TD]b3[/TD]
[/TR]
[TR]
[TD]b2[/TD]
[TD]Bananas[/TD]
[TD]6.3[/TD]
[TD]3[/TD]
[TD]b1[/TD]
[/TR]
[TR]
[TD]b3[/TD]
[TD]Bananas[/TD]
[TD]9.5[/TD]
[TD]1[/TD]
[TD]b2[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

I'm not sure what you have used for the SalesRank column but I presume it needs to allow for equal values so I have included a suggestion here in case your current formula does not do that.

Excel Workbook
	A	B	C	D	E
1	Salesman	Product	PredOutPut	SalesRank	SalesmanInOrder
2	a1	Apples	5.1	2	a3
3	a2	Apples	4.7	3	a1
4	a3	Apples	8.3	1	a2
5	b1	Bananas	7.3	2	b3
6	b2	Bananas	6.3	3	b1
7	b3	Bananas	9.5	1	b2
8	a	Pears	6	5	d
9	b	Pears	7	2	b
10	c	Pears	7	3	c
11	d	Pears	8	1	e
12	e	Pears	7	4	a
13	f	Pears	2	6	f
Order

 

[Roghaltz]

That worked perfectly! Thank you very much. It would have taken me forever to figure it all out.

 

[Peter_SSs]

That worked perfectly! Thank you very much. It would have taken me forever to figure it all out.

You're welcome.