I am running a report on Excel 2019 which has 2 sheets, and I want to automate the value in the field called Status in the sheet2 with the new status on sheet1 where a new row was added for the same item.

Ex:

thank you.

I tried index match, but a random update pull the data and new row is added down below and I lost my references. The Max function is good as well considering the last date of status was pulled, but I need to select the whole column and the max function just bring me the same last date for everyone.

I want to get from sheet1 the last status for the same login updated in a new row added and take to sheet 2.

CodePudding user response：

May be there is more eloquent way to do this, however this is what I have tried,

• Formula used in cell F3

=INDEX($A$3:$A$22,MAX(IFERROR(MATCH(
ROW($C$3:$C$22)&E3,ROW($C$3:$C$22)&$C$3:$C$22,0),"")))

Since its an array formula, you may need to press the CTRL SHIFT ENTER depending on your Excel Version

CodePudding user response：

Lookup Last Occurrences Using LOOKUP

If Status is in Sheet1!A1 and STATUS is in Sheet2!B1 then in Sheet2!B2 use the following formula:

=LOOKUP(2,1/(Sheet1!C$2:C$21=A2),Sheet1!A$2:A$21)

and copy down.