Need a formula to return text instead of a number

smallarmz

New member
Joined
Nov 21, 2013
Messages
3
Reaction score
0
Points
0
Hi guys, hope everyone is having a good day!

I am putting a simple spreadsheet together for the tanning company I work at (not getting paid for it, just doing it in my spare time). I need this spreadsheet to find the 5 highest selling lotions for a month. Instead of just returning the 5 highest values though, I would like for a formula to return the name of the top 5 highest selling lotions. One of my concerns is that the range of data is separated into 3 different columns. (because we sell 3 different types of lotion)

This is what I have so far:
Lotion Sales.jpg

The data for units sold is in columns C, G, and K. I need the 5 highest values from all 3 columns combined (not 5 from each column). I would like a formula to calculate and display the names of the lotion, which are in columns B, F, and J, instead of just returning the 5 values in column C, G, and K.

Thanks!
 

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
2010
Hi
Looking at the products they all appear to be different, so is there no "adding together" to be done ?
or
Should Snooki be the highest with 24, as they have sales in all three categories ?
 

smallarmz

New member
Joined
Nov 21, 2013
Messages
3
Reaction score
0
Points
0
No, none of the products should be added together, they are all separate, even if they share the same name.
Also, no, Snooki should not be combined. There are 3 different types of snooki lotion. I see where that could be confusing.

I want to find the 5 highest selling lotions of all the three columns. In the pic I posted, White 2 Black Extreme has 11 units sold and then Go Dark, Totally Baked, Girlfriend Sexy, and SWAG all have 10 units sold. Those are the 5 highest selling lotions. I want a formula that will associate the name of the lotion with the number of units sold. This is so that I do not have to manually look through the data and determine the 5 highest values myself.

I can use the LARGE function to return the 5 highest values from columns C, G, and K. However, instead of the numerical values, I want it to return the names of the lotions that are next to the numerical values.
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
If you can place the table below each other instead to line up the items in the same column, then it would be much easier....
 

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
2010
If you can place the table below each other instead to line up the items in the same column, then it would be much easier....

Yes :)


This works if you change the table:

The formulae are array ones so after entering you need to press CTRL+SHIFT+ENTER instead of just ENTER.
 

Attachments

  • IndexCreams.xlsx
    12.6 KB · Views: 18

smallarmz

New member
Joined
Nov 21, 2013
Messages
3
Reaction score
0
Points
0
You guys are awesome!

Hercules, you are my hero :kiss: Thank you so much for all your help.

There is no way I would have every figured out that super long formula on my own lol.

Hope you guys have a great day!
 

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
2010
Your Welcome :)
Just one point - The formula is good for about 4000 rows of data. If you need more than that, you will see the figure 10000 mentioned in two places. Amend these to 100000 in H21 and I21 (CTRL+SHIFT+ENTER)
Then copy the formulae down to H25 and I25.
Adjust the above to suit your actual cell references, of course.
 
Top