Formula to use to find top 3 and the adjacent cell label?

[Caveman1964]

Hi,
Need help with a formula that will find the top 3 in a table and place the item in cell before.
In bottom table, D will have formula that will pick up 420, and C2 will pick up SQuash, then D3 will pick up 325 and C2 will pick up Okra. and so on.
I found how to use =large to pick up in order of top sold but cant find how to get the adjacent cell also. So C2 and D2 needs its own formula......spent a few days on this and just can't get it.
any help will be much appreciated.
Thanks!

	A	B
1	Item	Sold lbs
2	Lettuce	88
3	Tomatoes	248
4	Onions	85
5	Okra	325
6	Squash	420
7	Bell Pepper	45
8	Green Beans	180
9	Field peas	135

<tbody>
</tbody>

	C	D
1	Item	Top 3
2
3
4

<tbody>
</tbody>

 

[Legacy 436357]

Try this : (Sorry had it backwards, now it's fine)

Sheet1

	A	B	C	D
Item	Sold lbs
Lettuce
Tomatoes
Onions

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:150.4px;"><col style="width:85.6px;"><col style="width:85.6px;"><col style="width:85.6px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

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

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="align: right"]88[/TD]
[TD="align: center"]Squash[/TD]
[TD="align: right"]420[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: right"]248[/TD]
[TD="align: center"]Okra[/TD]
[TD="align: right"]325[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="align: right"]85[/TD]
[TD="align: center"]Tomatoes[/TD]
[TD="align: right"]248[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula C2 =IFERROR(INDEX(A:A, AGGREGATE(15, 7, ROW(A:A)/(B$1:B$8=D2), COUNTIF(D$2:D2, D2))),"") D2 =IFERROR(LARGE(B$2:B$8, ROW(1:1)),"") C3 =IFERROR(INDEX(A:A, AGGREGATE(15, 7, ROW(A:A)/(B$1:B$8=D3), COUNTIF(D$2:D3, D3))),"") D3 =IFERROR(LARGE(B$2:B$8, ROW(2:2)),"") C4 =IFERROR(INDEX(A:A, AGGREGATE(15, 7, ROW(A:A)/(B$1:B$8=D4), COUNTIF(D$2:D4, D4))),"") D4 =IFERROR(LARGE(B$2:B$8, ROW(3:3)),"") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4



Excel tables to the web >> Excel Jeanie HTML 4

 

[Caveman1964]

It works! Great! Thank you!

 

[Legacy 436357]

You're welcome!