Find row number problem

[Opal]

Hoping someone can help me out.... I am working in a spreadsheet with almost 10,000 rows of data and growing. I have an array formula that provides me with the largest (5) value within a date range as follows:

{=LARGE(IF(Dates>=$A$5,IF(Dates<=EOMONTH($A$16,0),IF(Zones={1,2,3},LineStop))),F5)}

This works great. But I also want to find out what row this formula result is in so that I can get the data from a different column on the same row. I have tried the following:

{=MATCH(LARGE(IF(Dates>=$A$5,IF(Dates<=EOMONTH($A$16,0),IF(Zones={1,2,3},LineStop))),F5),LineStop,0)}

However, it is returning the first row in which the same number appears and not the row containing the formula result.

Rank Line stop Row#
1 480 9430
2 119 9671
3 60 169
4 53 3494
5 49 196

I should be getting results from row 8900 to 9843 in the Row# column. Does anyone have any suggestions? Thank you.

 

[crazydragon84]

i don't get it, if the dates are all in one column why can't you just use "=Large(column[],1)"? Why do you need the array formula?

To find row number, you can just use match. Match(Date you are looking for, column dates are in, 0)

 

[Opal]

i don't get it, if the dates are all in one column why can't you just use "=Large(column[],1)"? Why do you need the array formula?

To find row number, you can just use match. Match(Date you are looking for, column dates are in, 0)

I am looking for the largest value in my Line stop column between a specific date range that is why I am using an array.

 

[crazydragon84]

Without looking at an example data its hard to understand what you are doing (your Rank thing doesn't contain any dates, so its confusing).

Also you can use a Maxif Function to calc the largest date within the specific date range, and for the second one you can make the max date equal to the first one and so forth.

What do you do if you have 2 dates that are the same?

 

[Opal]

Without looking at an example data its hard to understand what you are doing (your Rank thing doesn't contain any dates, so its confusing).

Also you can use a Maxif Function to calc the largest date within the specific date range, and for the second one you can make the max date equal to the first one and so forth.

What do you do if you have 2 dates that are the same?

Unfortunately, I cannot share the file. What I showed in my example was the formula results. Also I have multiple dates that are the same. Let me see if this is more clear - the raw data is a column of Dates from Jan to Dec called "Dates", a column of line stop data (how many minutes the equipment was down) called "Linestop" ,a column of data with the Zone (1,2,3, 4 or 5) called "Zones", and a column of data with the equipment name called "Equipment":

I used the rank column in my formula results example to show that I was looking for the 1st largest value, then the second, and so on as I need the Top 5.

Column Column Column Column
F G H I
Row 5 1 480 9430 Equipment name
Row 6 2 119 9671 ...
Row 7 3 60 169 ...
Row 8 4 53 3494 ...
Row 9 5 49 196 ...

The formula in G5 is: {=LARGE(IF(Dates>=$A$5,IF(Dates<=EOMONTH($A$16,0),IF(Zones={1,2,3},LineStop))),F5)}
which returns the largest linestop value between Jan 1 and Dec 31st for three zones.

The formula in G6 is: {=LARGE(IF(Dates>=$A$5,IF(Dates<=EOMONTH($A$16,0),IF(Zones={1,2,3},LineStop))),F6)}
which returns the second largest linestop value between the same dates for the three zones and so on...

What I want to determine is the row number that this value appears in so I can use the INDEX function to give me the equipment name. For my data all of it appear from row 8900 to row 9843 so all the values in column H should be between these two values and they are not. Apparently using MATCH with LARGE in an array formula does not work to provide the results I need so I need another option. Any ideas?

 

[crazydragon84]

hmmm i'm still not fully understanding and maybe i'm over simplifying. is it as easy as adding a rank in the date column in your data, then doing a vlookup on the rank? the only thing is if there are multiple same max dates (e.g you have two lines with 12/31/2017) then you need two columns that does ranking (one that just does rank, then second column that does countif on the rank for any lines above that line and adds it to the rank).

Date Zone Equipment Rank1 Rank2
12/31/17 1 XYZ 1 1
12/31/17 2 ABC 1 2
12/31/17 3 DEF 1 3
8/18/17 4 JIY 5 5
7/16/1 5 TUS 6 6
9/13/17 1 QID 4 4

Something like this. First column is =Rank(column with Dates) Second column is =[Rank1 value] + countif([Rank1 value], Rank1 Column (but only up to row of the formula))

does that make sense? then you can use the rank2 column to find 1, 2, 3,4, 5?

 

[shift-del]

Opal,

this is untested.

[COLOR=#333333]{=MATCH([/COLOR][COLOR=#0000cd]LARGE(IF(Dates>=$A$5,IF(Dates<=EOMONTH($A$16,0),IF(Zones={1,2,3},LineStop))),F5)[/COLOR][COLOR=#333333],[/COLOR] [COLOR=#ff8c00] IF(Dates>=$A$5,IF(Dates<=EOMONTH($A$16,0),IF(Zones={1,2,3},LineStop)))[/COLOR] [COLOR=#333333] ,0)}[/COLOR]

 

[Opal]

Sorry, crazydragon that's not what I am looking for.

 

[Opal]

Shift-del, I believe that is the logic I am looking for... I will test it tomorrow. Thank you for taking a look.

 

[Opal]

Shift-del, I believe that is the logic I am looking for... I will test it tomorrow. Thank you for taking a look.

Shift-del... unfortunately, that yields a #N/A error....