Match function question

Is there a way to use the match function but for it to skip the first match found and look for the second or third match?

What I've got so far is a formula that first uses the large function to find the first, second, third, etc... highest number in a column. I then use the match function to locate how many rows down it is at. The problem I've got is that sometimes there's an identical number in the data list. The large function will say the same number is the third and fourth highest number in the data list but the match function will only find the first value listed so it finds the same row value for the third and fourth highest number. Thanks in advance for any help

The way my workbook is setup is like this. There are two sheets, one that I paste a data list into that contains an item name, price, unit and sales total.

Please Login or Register  to view this content.

The second sheet takes that data and creates a top 20 list based on most units. Currently I'm using a formula that uses the following functions:
1. Large - finds the first, second, third, etc.. highest unit total
2. Match - takes the large function result and finds the row #
3. Index - takes the match # for the row# and I change the column # to display the item name, price, units, and sales

The problem I've run into is that when items have the same unit total, the match function won't work properly. As an example, say the second and third highest unit totals were both 23. The large function will give a value of 23 for the second and third highest values. The match function will then go and search for the #23 and find the first instance of it. The match function will then display the same exact row # for the second and third highest value. I need a way for the match function to skip the first instance of the # if it had just found that number.

This is the formula I've go so far:

=INDEX('Data Sheet'!$A:$D,(MATCH(LARGE('Data Sheet'!$C:$C,3),'Data Sheet'!$C:$C,0)),3)

This is finding the third largest number in column C, then finding the row #, then index looking up that row # in column 3 (i.e. C) which displays the third highest unit total.

I don't understand how you have your formula setup in relation to my data. I was also hoping not having to alter my formula too much.

Say I wanted to test it out with just item names in column A and total units in column B with data then rearranged to display the top 5 units with the item name in column C and the unit total in column D. Thanks

I only posted in the another forum after I wasn't figuring it out here. I'm at a deadline to get this done and I figured I'd ask another site in case they knew. I still don't have a solution yet and I'm desperate. Thanks

I've cross-posted this topic here:

http://www.mrexcel.com/forum/showthr...96#post1582896

I've read the forums rules page and if I ever post in multiple forums I will make sure to display the url to the cross-post. Thanks