# Thread: Need a formula to return text instead of a number

1. ## Need a formula to return text instead of a number

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:

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!

2. 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 ?

3. 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.

4. 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....

5. Originally Posted by NBVC
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.

6. You guys are awesome!

Hercules, you are my hero 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!

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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•