Ok I need help from some of the Spreadsheet gurus if there are any here.I have a data set the contains 2 columns the first column lists the States and territories the second column is the number of occurrences a particular event occurs in each. What I need help with is I need a function that will return the state from the first column based on it being the max value in the second column and if there is a tie for max value that each state that is a max value is returned. I have a function that works for the first part but it will only return the first max value it reaches in the list and ignores any others with the same value.For example My current formula =INDIRECT("A"&MATCH(MAX(C1:C60),C1:C60,0)) if 5 is the max number of occurrences, and Arizona, Colorado, and Texas each have 5 then my current formula only returns Arizona. I would like it to return all three, ideally separated by commas.Any help is greatly appreciated. Also the spreadsheet is created using OpenOffice.Thank You

- Wolfman213
**Posts:**1377**Joined:**Fri Sep 05, 2008 12:58 am**Location:**Anderson, IN

If you are wanting it to pull and of the matching hgih states in one cell, you probably will have to get into the VB side of things (if OpenOffice does that) bc as far as I know, that will be way more complex than a formula can pull since it will probably end up in a circular reference error. If you want them to be listed say in A1:A? then it can be done. I found a decent formula the other day in regards to this and I'll try looking it up again. If involved the "IFERROR" function which I've actually been utilizing a lot more recently and seeing it's potential.The vlookup will only return one answer as well. The MAX function helps in determining the largest number for you, but I've not been able to think or find anything that well return all the results in one cell using formulas alone and not actual written VB code.

April 2010 Co-MOTM * Custom LED Gauge Cluster swap * LED dome, HVAC, and shifter * Retrofit projector w/ blue halo * Kenwood KDC-HD942U * Herculined cargo area * Debadged *Wolfman's Garage

VLookup returned one answer per the cell you are correllating to. If you utilize the $ sign before each letter/number of the cells when first inputting the formula and then drag down, you can find results based on the total of the column you are picking from in relation to each individual cell in the other column.sound confusing, it is. VLOOKUP is not an easy formula to get the hang out without trial and error, but when it works, it is awesome!You can also try making a Pivot Table.

2012 GLI Autobahn 6MT

- Wolfman213
**Posts:**1377**Joined:**Fri Sep 05, 2008 12:58 am**Location:**Anderson, IN

But if he has 50 states that he's going through, then he'll also have to have 50 cells to look at rather than have all the information in either one cell or listed together based on how many actually meet the criteria of the highest.Not familiar with Pivot Tables at all lol.Here is code out on the web that will at least give you only the ones meeting your criteria. Granted, it will not place them in one cell, but just fill the corresponding cells.=IF(ISERROR(INDEX($A$1:$B$50,SMALL(IF($A$1:$A$50=$A$51,ROW($A$1:$A$50)),ROW(1:1)),2)),"",INDEX($A$1:$B$7,SMALL(IF($A$1:$A$50=$A$51,ROW($A$1:$A$50)),ROW(1:1)),2))In order for this to work, here's somethings you will need to do. Place the numbers in column A and the states in column B. In A51, us the formula =MAX($A$1:$A$50) when you place the larger formula in a cell, you MUST hit (CTRL+SHIFT+ENTER) so it will place it as an array, otherwise it will not work. This should get you started at least.

April 2010 Co-MOTM * Custom LED Gauge Cluster swap * LED dome, HVAC, and shifter * Retrofit projector w/ blue halo * Kenwood KDC-HD942U * Herculined cargo area * Debadged *Wolfman's Garage

My pc is snafu so posting from memory from the droid (so i might not have it exact)You could pick another column and set formula to be something likeif(C1=MAX($C$1:$C$60),A1&", ","") so that cell will contain the name of the state followed by a comma and a space if that state's value is the max or nothing if notDo that for every row in D or do it in D1 and copy it into D2:D60 (changing the C1 and A1 appropriately)Then somewhere else, say D61, set a cell to be =CONCATENATE(D1,D2,D3,..........D60) That should give you a list of all the high states separated by a comma and a space. Finally, where you want the end result to show up set that cell to be =SUBSTITUE(D61,", ","",COUNTIF(D1:D60,"*, "))This counts the number of items in column D that end in comma space and substitues the last one will nothing, pruning the trailing comma space.from cell D61

- Wolfman213
**Posts:**1377**Joined:**Fri Sep 05, 2008 12:58 am**Location:**Anderson, IN

(removed)!!!!!!!!! I can't believe I didn't think of that!!!! And the fact you spouted all that off from memory without a computer in front of you, BRAVO Joatman!!!!!!!!!!!!

April 2010 Co-MOTM * Custom LED Gauge Cluster swap * LED dome, HVAC, and shifter * Retrofit projector w/ blue halo * Kenwood KDC-HD942U * Herculined cargo area * Debadged *Wolfman's Garage