Index, Match, Multiple Results

**ecorf** · 07-28-2013, 12:18 AM

Hi,

I am trying to use the index, match formula to display the top ten results for the year for ozone concentration. There may be more than one day with the exact same concentration. My problem is for those days that have the exact same concentration, I can not get the other dates to appear in my first column. It only displays the first date for the first time that concentration occurs.

In my DATA! Spreadsheet which contains the dates and the concentrations.

July 7 has a Concentration of 65 and AQI of 67 ALSO ON
July 17 that same concentration of 65 , AQI of 67 occurred.

I can not get the second instance to display 7/17, it only displays 7/7.

In the 3 columns below, here is the formula I am using for the second column Con.

=LARGE(DATA!$B$3:$B$368,8) - This is the eighth row of data below.
=LARGE(DATA!$B$3:$B$368,9) - This would be the ninth row of data below.

I mention the second column formula because I "assume" I haven't made a mistake there.

The formula that I use for the first column Date is:

=INDEX(DATA!$C$3:$C$368,MATCH(LARGE(DATA!$B$3:$B$368,8),DATA!$B$3:$B$368,0)) - this is the eighth row of data formula
=INDEX(DATA!$C$3:$C$368,MATCH(LARGE(DATA!$B$3:$B$368,9),DATA!$B$3:$B$368,0)) - this is the ninth row of data formula

Date Con. AQI
6/29 77 104
6/28 72 90
7/3 71 87
6/30 70 84
7/6 69 80
5/31 67 74
7/1 66 71
7/7 65 67
7/7 65 67
7/28 64 64

Any thoughts? I appreciate it.

**FDibbins** · 07-28-2013, 12:34 AM

Hi and welcome to the forum

index/match and vlookup will generally only ever return the 1st instance of what they are searching for. In order to find the next instance of the same criteria, you need to add a tie-breaker (I normally use a helper column for that), and use something like =A2+(countif($A$2:A2)/100) copied down (there are a number of tricks for this), and then base the index/match on that

As that formula is copied down, it will progressively test to see if the value in A has been repeated. If not, just take the value in A. But if it has, it will keep a running count of the repeats, divide them by 100 each time (you could use 10, I prefer 100 because it allows for 100 repeats). It then adds the count/100 back to the value of A.

If A is text, then use & instead of +

If you still have a problem, I would suggest you upload a sample work book, (all sensitive data removed), showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

**ecorf** · 07-28-2013, 12:36 AM

Thanks for the tip and the welcome to the forum. I will give that a try then post back later with my results. Thanks again!

Sample file attached.

**ecorf** · 07-28-2013, 11:31 AM

Thanks! That did the trick. Appreciate that!

=B3+(COUNTIF($B$3:B3,B3)/100) Had to add that, B3 in there.

Attached is the resolved sample spreadsheet.