Is there a function for Excel 2019 like index match or vlookup to find a value in a row added cross sheet?

Question

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.

Answer 1

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.

Answer 2

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