Best way to return a row number from MIN array formula

[wallock]

Hi!

I have the following formula:

{=MIN(IF(M2=Product,IF(AM2=PatientID,IF(ROW(L2)<>ROW(FillDate),IF(L2<=FDate,FDate)))))}

This returns the next subsequent date (Column L) given the conditions as set forth. I want it to return the row number of that date, however. There are duplicate dates throughout the sheet. The only way I could figure out how to do it was by using the following formula:

{=IFERROR(MATCH(MIN(IF(M2=Product,IF(AM2=PatientID,IF(ROW(L2)<>ROW(FDate),IF(L2<=FDate,FDate))))),IF(M2=Product,IF(AM2=PatientID,IF(ROW(L2)<>ROW(FDate),IF(L2<=FDate,FDate)))),0)+1,"")}

This, in essence, doubles the amount of time by calculating the array twice. Is there any way to just return the row number using the first formula, other than calculating a whole duplicate array?

[XOR LX]

Hi.

If you could attach a workbook with an example and your expected results then I'm sure we'll be able to help.

Regards

[Pete_UK]

I presume FDate is a named range of dates, so if these are in sequence down the column, you could try using ROW(FDate) instead as the final term in your first formula, and then the MIN function will find the smallest row that contains the date. Note that you might need to subtract the number of rows above the table if you want the relative row number (as MATCH would return).

Hope this helps.

Pete

[wallock]

I presume FDate is a named range of dates, so if these are in sequence down the column, you could try using ROW(FDate) instead as the final term in your first formula, and then the MIN function will find the smallest row that contains the date. Note that you might need to subtract the number of rows above the table if you want the relative row number (as MATCH would return).

Hope this helps.

Pete

Thank you, unfortunately they are not in sequence. I realize I could sort them, but I'm trying to come up with a template that will be used by numerous people without any user assistance other than copying and pasting the initial data (thus, sorting is out of the question). If I use row(FDate) it seems to just return the smallest row number on the array.

[Pete_UK]

... it seems to just return the smallest row number on the array ...

Well, yes, it only works if the dates are in sequence, which you now say is not the case. Please attach a sample workbook, as XOR LX has requested.

Pete

[wallock]

Sample2.xlsx

Alright, the sample document should be attached with the current working formula. Let me know if you have any questions and thank you greatly for taking a look.

[wallock]

Attachment 356277

Alright, the sample document should be attached with the current working formula. Let me know if you have any questions and thank you greatly for taking a look.

Any thoughts?