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

Can anyone help with the following:

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

Task for Roger.PNG


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).

Hi
can you please attach the excel and not the picture ?

Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

Hi Belinda,

Thanks for the heads up - I have attached an excel file with the corresponding problem. Looking for the value in D:D ("counter value and main counter avg. change per day") for each of the "unit no.s" in G:G based on the latest "counter reading date" in C:C.

Values in G:G are unique values found using advanced filter that was applied to E:E.

Really appreciate any help as I can't seem to get it to work with my index & match method.

Hi,
In g you can use:
=UNIQUE(E2:E20)

H2 and down:
=INDEX($D$2:$D$20,MATCH(AGGREGATE(14,6,(($E$2:$E$20=G2)*($C$2:$C$20)),1)&G2,$C$2:$C$20&$E$2:$E$20,0))