Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Top 10 items sold

Posted on 2013-06-17
Medium Priority
235 Views
I'm trying to create a list of the top 10 items with this largest sales \$\$\$ in my file. I know a pivot table would be the best approach but I don't want to utilize that for this file. I have a worksheet called "Database" where all my detail is stored. I'm using this formula to find the list but I also need to add a variable to it and only find the top 10 where column D = the value in cell A1. So when I need it to only look at the rows that the value in column D is the same in cell Al. So I need to modify this formula to account for this variable. Any ideas?

Column B = Item
Column I = Total Sales \$\$\$

``````INDEX(B:B,MATCH(LARGE(I:I,ROW()-2),I:I,0))
``````
0
Question by:Lawrence Salvucci
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 4
• 4

LVL 23

Expert Comment

ID: 39253109
Perhaps?

=INDEX(B:B,MATCH(1,INDEX((I:I=LARGE(I:I,ROW()-2))*(D:D=A1),0),0))

but you may find it more efficient not to use whole column references, and instead limit the column sizes (e.g. B\$1:B\$1000).... as this is really an array formula.
0

LVL 1

Author Comment

ID: 39253140
That works but how do I get it to find all the top 10 items and not just the top item?
0

LVL 23

Expert Comment

ID: 39253190
Maybe something like:

=INDEX(\$B\$1:\$B\$10,MATCH(1,INDEX((\$I\$1:\$I\$10=LARGE(\$I\$1:\$I\$10,ROWS(\$A\$1:\$A1)))*(\$D\$1:\$D\$10=\$A\$1),0),0))

copied down.

If still not right, please post sample workbook....
0

LVL 1

Author Comment

ID: 39253211
Ok let me try this new formula first. What is ROWS(\$A\$1:\$A1) represent? I didn't have that column in my original formula. Just not sure which column to use for that to try your new formula
0

LVL 23

Expert Comment

ID: 39253246
It's used as an incrementer for the k value in the LARGE() function.

e.g.  ROWS(\$A\$1:\$A1) is equal to 1 (so Largest)
as you copy down, ROWS(\$A\$1:\$A2) is equal to 2 (so 2nd largest), etc...
0

LVL 1

Author Comment

ID: 39253438
Ok I've attached a sample worksheet for you. I changed the columns around a bit so I could just create this sample file without all the other stuff that's in the file. But as you can see columns A, B, & C is where the data is. The match for column B is in cell G2. And then I need to list the top 10 items and sales \$\$\$ in columns K & L.
Sample-File.xls
0

LVL 23

Accepted Solution

NBVC earned 2000 total points
ID: 39253530
Ok,

Try, in L2:

=LARGE((INDEX((\$B\$14:\$B\$182=\$G\$2)*(\$C\$14:\$C\$182),0)),ROWS(\$L\$2:\$L2))

copied down

Then in K2:

=INDEX(\$A\$14:\$A\$182,MATCH(1,INDEX((\$B\$14:\$B\$182=\$G\$2)*(\$C\$14:\$C\$182=L2),0),0))

copied down.
0

LVL 1

Author Closing Comment

ID: 39253569
Works like a charm! Thank you for all your help! I greatly appreciate it!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calculâ€¦
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
###### Suggested Courses
Course of the Month9 days, 21 hours left to enroll