Match function question

**Weasel** · 05-30-2008, 01:41 PM

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

**NBVC** · 05-30-2008, 01:47 PM

Say your data is in A2:B10

and you list the Largest values from A2:A10 in ascending order in C1:C5,

Then use formula in D1:

=Index($B$2:$B$10,Small(IF($A$2:$A$10=C1,Row($A$2:$A$10)-Row($A$2)+1),Countif($C$1:$C1,$C1)))

confirmed with CTRL+SHIFT+ENTER and copied down.

**Weasel** · 05-30-2008, 02:16 PM

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.

Column A  Column B  Column C  Column D
Item Name   Price    Units   Sales

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.

**NBVC** · 05-30-2008, 02:19 PM

So you didn't see/try my suggestion?

Try to adapt it to your data.

**Weasel** · 05-30-2008, 02:35 PM

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