Find the best seller??

Hey this is hard to explain, I want a 'Top sellers' table.

So for example,

item 1 sold 23
item 2 sold 333
item 3 sold 1

I want a cell that says

Best Seller - item 2

I can find the highest number by doing =max(range) but I don't want the higest number in my best sellers table, I want what the item is called.

It's hard so example so I've attached said sheet below. I've highlighted the best seller table in RED.

Thank in advance,

Hows this:

PHP Code: 

=INDEX(B7:B34,MATCH(MAX(C7:C34),C7:C34),0) 


Returns an error, not sure what's being done there.

---------- Post added at 11:56 PM ---------- Previous post was at 11:51 PM ----------

Sorry my bad works fine.

Whats happening there? What does the index refer too?

It's a bit like a Vlookup.

The Match returns the line number of the Maximum value found. Index takes that line number and returns that number from B7:B34

Thanks Kyle!

Sorry I have to re-open this, the index formula isn't working when I'm putting in different data. How can I do this via VLOOKUP?

Thanks,

Hi benwall

It works, what other data are you putting in and where.

I've extended the table and tweaked it slightly and after copying in fresh data it doesn't work. It just seems quite unstable or maybe it's just me being stupid.

Helo,

I've uploaded two files the first one labelled 'Orignal' works fine.

The second one called 'Original2' I've changed the formulas in 'Items Sold' column which shouldn't effect the index area but it does and it no longer works.

Any help is appreciated.

Thanks,

Note the 0 in the match function

PHP Code: 

=INDEX(C6:C61,MATCH(MAX(D6:D61),D6:D61,0),0) 


What does the 0 relate to?

An exact match

Sorry for being thick here. How does changing the items sold column mean it doesn't work because of the 0?

Because by default the function uses 1 rather than 0, this means it returns the largest value that is less than or equal to the lookup, however your data must be sorted in ascending order. Using forces an exact match - the original function I gave you should have had a 0 in too, but I missed it out

Arr ok.

Thanks.

Thanks Kyle.. appreciated.