How to get returning data from the latest input that match each key cell

0
votes

How to make status column get the latest status for each motor code from log record. I also attached the sample sheet here please help, thank you.

https://docs.google.com/spreadsheets/d/1FObfeQ652jfLy5M_pFQcEgnLoOYmdvJ_mC1gn2QldBY/edit#gid=0

1
google-sheets

1 Answers

0
votes

Use QUERY() with ROW() - paste one and fill down

=QUERY({ARRAYFORMULA(ROW('Mortor Status'!B$2:B)),'Mortor Status'!B$2:C},"select Col3 where Col2 ='"&A2&"' order by Col1 desc limit 1",0)

ARRAYFORMULA(ROW()) to get number of each row to sort desc

select Col3 to get only one column which is "Status"

where Col2 ='"&A2&"' to filter only "Code" like S0-01 ,...

order by...desc to re-sort to get the last one to the top

limit 1 to get only one row to be able to fill next row

I hope it'll help you