LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Ted Metro
 
Posts: n/a
Default Index lookup and duplicate numbers

I have a data set that looks like this starting at A1

name accounts sales
dave 21 2000
bob 12 800
mike 8 375
chris 20 850
john 25 1900
brad 21 2800

To find the name that has the largest number of accounts I use a formula
like this --

index(a2:c7,match(large(b2:b7,1),b2:b7,0),1)

To get the 2nd largest I just change the value in the large function to
(b2:b7,2).

The problem is that there are two people with 21 accounts so the large
function is getting stuck. I'd like the modify the formula so that sales is
used as a secondary filter so that if the number of accounts is equal then
sales is the deciding number with higher sales being better.

This has to be pretty easy, but I can't figure out how to get sales to be
the tie-breaker so to speak.

Help Excel masters!

Ted
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Not Lookup Jonny Excel Worksheet Functions 3 June 10th 05 07:16 PM
Lookup Problem Scott Excel Worksheet Functions 0 April 18th 05 08:07 PM
lookup or table, not sure what to do [email protected] Excel Worksheet Functions 2 February 17th 05 09:36 PM
lookup with 2 criteria LoriM Excel Discussion (Misc queries) 0 January 5th 05 02:05 AM
lookup help with multiple hits O'C Excel Worksheet Functions 0 November 11th 04 09:28 PM


All times are GMT +1. The time now is 06:43 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"