2

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:

lOGIN STATUS

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.

David Knight
  • 49
  • 5

2 Answers2

2

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.

VBasic2008
  • 44,888
  • 5
  • 17
  • 28
1

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

FORMULA_SOLUTION

• 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

Mayukh Bhattacharya
  • 12,541
  • 5
  • 21
  • 32
  • 1
    Wow, that's fantastic... it works!!!. Now I am going to try cross sheet. For now that's wonderful, thank you so much Mayukh Bhattacharya!!! – David Knight Mar 29 '22 at 15:20