Formula help needed to extract cell value based on max if array.

Hi All, I hope you may be able to help me.

I have a spreadsheet with 122 rows. I have used the date function to convert 3 column cells to a date which I can manipulate. I have a requirement whereby I need to extract a cell value and copy this value to another cell location based on certain criteria. There are multiple rows which have the same username but have different dates. I have used the min and max function with arrays to extract the most recent and oldest dates and assign them to their appropriate columns using the formula below:

This has worked well. B2-B122 references the username column (which has more than one instance) and F2-F122 references the date column.

=MIN(IF(B$2:$B$122=B2,F$2:$F$122))
=MAX(IF(B$2:$B$122=B2,F$2:$F$122))

I’d now like to extract specific cell values - L2-L122 for each of the max date rows – not the min date rows. I’ve tried the following but this does not work.
=INDEX(L2:L122,MAX(IF(B$2:$B$122=B2,F$2:$F$122)),1,0)

If possible I'd also like to discard any records which only have one username listed, in other word only one instance in the B column. A string could perhaps be inserted into the new column which will contain the indexed values which says only 1 record or similar.

Any help would be greatly appreciated. Thanks in advance.
Wilbur

Hi, thanks for the quick reply. I have attached a before and after sheet and I'd appreciate any help you can provide.

Cheers Wilbur

Hi, I'll take a good look over this. It looks excellent so far. I'll get back to you and let you know how it goes. Thanks again.

Hi, that was most helpful and I really appreciate your quick response. The date is being converted and extracted and the array statement is being used in conjunction with the IF statement to select the min and max dates for a particular user id. I need to index specific cells only for the row that the most recent date applies to. In the example that you kindly sent to me there is a 3 instead of a 7 for user 4056. The celltoindex applies to the specific user row with the most recent date. Thanks again Glenn for your help.

My apologies that should have been a 3. A 3 should be there as it applies to the row with the most recent date. I need the cell contents of the celltoindex only for the row relating to the max formula. Sorry again for the confusion.

Brilliant..that works a treat. Many thanks. Do you know how I'd be able to hide the "one entry" rows and also the rows where a blank is present - just to make the data easier to read? Thank you Glenn for your help.

Thanks for your help thus far Glenn.

Can anyone possibly help me with presenting the Indexed cell value on the spreadsheet in another location. Currently it sits in the same row as the most recent date but I'd like it to be placed in the same row as the oldest or first date for that specific user. For example I'd like column c row 9 value to be moved to row 5 and column c row 3 to be moved to row 2. Many thanks for your help.

Okay, I have attached a before and after. I need the indexed cell for the most recent date listed in the first date row. I hope this makes sense. Thanks again.

Great, many thanks again for your help. Much appreciated. It is very reassuring that help is available from Excel wizards like yourself.