• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extract data

febausa

Member
I want to extract input data (from columns A:B) and make new cells output data (columns D:E).

I attached manual example.

Thank you for your help.

Febausa
 

Attachments

  • EXAMPLE FORMULA EXTRACT DATA-4.xlsx
    9.3 KB · Views: 0
In D2, confirm this formula as an array:
=IFERROR(INDEX(A$2:A$13,(1/MIN(IF(ISNA(MATCH(A$2:A$13,D$1:D1,0)),ROW(A$2:A$13))))^-1),"")

Confirm a formula as an array using Ctrl+Shift+Enter, not just Enter. Copy the formula down to D10.

In E2, put this formula:
=IF(D2="","",LOOKUP(D2,$A$2:$B$13))

Copy down to E2.

Assumption: Data in col A will be sorted.
 
Formula for E2 even if data in col A is unsorted:

=INDEX($B$2:$B$13,MAX(IF($A$2:$A$13=D2,ROW($A$2:$A$13)))-1)
Confirmed as an array formula with CSE.

It assumes you'll always want the Ball of the last entry of a date.
 
Back
Top