To retrieve X based on looking up Y and recent date

[Sruthy Malite]

Hi,

 Can anyone help me retrieve Unique Number based on QN with recent date .

Unique No:	QN	Assignee	Date
1	201006416	Charlie	14.08.2018
2	201172621	Ethan	01.08.2018
3	201172620	Ethan	15.08.2018
4	201006416	Ethan	18.08.2018
5	201172620	Ethan	01.08.2018
6	201172621	Ethan	15.08.2018
7	201172620	Arthur	21.08.2018
8	201172621	Charlie	11.08.2018

[#2]

Hi,

I do not understand your question.  What exact result are you expecting?

[#3]

Here QN which is Column B has many assignees on different dates Column D

I want to know the assignee for recent date or in other words who is the recent assignee for a QN.

OR

I am also good with getting the Unique no: (Column A) for recent assignee for a QN (Based on QN and date comparison)

[#4]

Hi,

1. Select range B1:B9 and paste in cell G1.
2. Select G1:G9 and go to Data > Remove Duplicates
3. In cell H2, enter this formula and copy down =MAXIFS($D$2:$D$9,$B$2:$B$9,G2).  This MAXIFS() function works in Excel 2016 only
4. In cell I2, enter this formula and copy down

=INDEX($C$2:$C$9,MATCH(1,INDEX(($B$2:$B$9=G2)*($D$2:$D$9=H2),,),0),1)

Hope this helps.

[#5]

Hi Ashish,

Mine is a 2013 Version excel and I am pulling an errror with =MAXIFS($D$2:$D$9,$B$2:$B$9,G2)

As you predicted

Could there be a way around?

Thank you very much for your support

[#6]

Hi,

In cell H2, enter this array formula (Ctrl+Shift+Enter) and copy down

=MAX(IF(B$2:B$9=G2,D$2:D$9))

Hope this helps.

[#7]

Hi Ashish,

I am facing attached error :(

Am i doing something stupid?

[#8]

Hi,

I do not usually participate in this forum so am not very comfortable using this platform.  Please post your question here and share the link of the post with me.  I'll help you there.

[#9]

Hi,

The link to questions is here

[#10]

Did you get it Ashish?