How to use LARGE to return a cell reference for use in OFFSET formula

Hi all,

Hope you are having a splendid day.

I'm trying to create a small table that will feed a pie chart to show the top five categories. This small table feeds off a much larger table and uses the LARGE formula to find the largest 5 categories and return that value. I would like to use formulas to obtain the names of the categories for use as labels in the pie chart, which are 13 columns to the left.

All this seems quite simple but I can't quite get it to work. I understand LARGE returns the value and not the cell reference so I've tried combining it with ADDRESS, so I can use it in OFFSET, but I just get an error that something is wrong with my formula or that too few arguments have been entered.

LARGE formula: =LARGE($O$18:$O$56,1)

My poor attempt returning an error: =OFFSET(ADDRESS(LARGE($O$18:$O$56,1)),0,-13))

I've also tried CELL, but think I'm missing how these functions work. I'd like to avoid array formulas where possible as there are already quite a few in the spreadsheet.

Any help appreciated.

Thanks, TC.

Upload sample file please

Hi

Thanks for coming back on this.

XOR LX, your formula works perfectly - until as you say there are two counts for the same value, which could happen. The underlying table is fixed (doesn't expand and contract) so the labels / names are always in column B - 18 to 56 and the total values in column O - 18 to 56. I'm more familiar with VBA where you would use the logic of returning the address for the found match (Large 1, 2, 3...) and then offset it - but appreciate excel functions work a bit differently. I wanted to avoid using Macros in this instance as it's quite simple (or so I thought) and users cannot be relied upon to enable them!

nflsales - I cannot upload a workbook as I'm at work and our security settings prevent uploading.

Thanks, TC

Sorry probably didn't make myself clear.

So lets say we're counting complaints categories relating to products. There are 39 different categories and the number of complaints is broken down month by month (Jan, Feb...) with a Totals Column in O that I'm using to find the overall highest ranking product complaints.
Lets continue to say that Power Adapters are the highest ranking with 36, followed by USB Cables at 25, Laptop Chargers at 25, Tablet Cases at 22 and Phone cases at 21.

USB cables and Laptop chargers both have the value of 25 - I don't mind which appears first in the list, it's irrelevant for creating a pie chart. However when I use your formula to return the category names then it just repeats the first one twice, eg:

36 Power Adapters
25 USB Cables
25 USB Cables
22 Tablet Cases
21 Phone Cases

I don't mind which way round they are but need Laptop Chargers to be showing as one of the 25's.

Hope this makes some sense, TC

=INDEX($P$18:$P$56,MATCH(LARGE($O$18:$O$56+ROW($O$18:$O$56)*0.000001,1),$O$18:$O$56+ROW($O$18:$O$56)*0.000001,0))
try this array formula

Thanks nlfsales - I get a #N/A error returned on this... "A value is not avaliable to the formula or function" - the ranges look correct - though I changed P to B.

can you post the formula you used

=index($b$18:$b$56,match(large($o$18:$o$56+row($o$18:$o$56)*0.000001,1),$o$18:$o$56+row($o$18:$o$56)*0.000001,0))

Thanks daddylonglegs that works great! I realised as I was typing that there would be a problem if the 4th, 5th, 6th values for example were all tied, but I don't think that's too big a deal in this case - there's unlikely to be tied values every month and it's just to give an general ideas as to where the problems lie.

Thanks to all for your assistance. TC