How to find a value based on a last date and a unique criteria

0
votes

Can anyone help with the following:

How to find a value based on last date and a unique criteria

In Column G:G, I have a range of dates. In column H:H, I have a value that consists of two separate values that I merged by =E2&" "&F2). I merged these values as i thought it would make the process more simple. In column J:J, I have a range of unit numbers. In Column N:N, I have then used the "advanced" filter function to create a list of unique unit numbers based on the range in column J:J.

I would now like to find the latest value in column H:H based on the latest date in column G:G for each unique unit number in column N:N. Does anyone have any good ideas?. Would be so greatly appreciated as i've been stuck on this exercise for hours.

I am using Office 365 and using Excel version 1902 (Build 11328.20644).

1
excelexcel-formula

1 Answers

0
votes

Is this what you mean?:

=INDEX(H$1:H$13,MATCH(1,(J$1:J$13=N2)*(G$1:G$13=MAXIFS(G$1:G$13,J$1:J$13,N2)),0))

Enter this formula in a new column in row 2 and drag down. Depending on your version of excel you may need to enter this array-formula with ctrl + shift + enter

It indexes column H with the row number of the maximum date value for the resembling unit no.