How to match two columns in Google Sheets and find the last match?

Question

Google Sheets

I'm not very familiar with Excel, I can not find the right function.

How to match two columns in Excel and find the last match?

I have a google table , in which data from a google form is added , and there is also a second table in which I want to make statuses, such as which car is free and which is busy.

Example: Vasya Pupkin took a certain car, filled it out in a Google form, an entry was added at the end of the table and in the second table opposite a certain car it changed to "Take", and then he filled out the table again and put "Passed", and in the column opposite machine changed to Passed.

I don’t understand how to do it ...

I tried this formula:

=IF(AND(LARGE('Контроль авто'!A:A;1);MATCH(A2;'Контроль авто'!C:C);MATCH(A14;'Контроль авто'!G:G));"Взял";"Сдал")

But it seems that she only takes it from the top, not from the bottom ... I tried to make the sorting from bottom to top, so that entries appear on top, it didn’t work out ...

Answer 1

It seems that it turned out to do what you wanted, though through a small crutch.
I've added an auxiliary sheet "Control auto (sort)". A single function is inserted there: =sort('Auto control'!A2:G ; 1 ; false). It sorts records in descending order by date. That is, the first entry is the newest.
And already on the basis of this sheet, I inserted formulas into the "Auto" sheet: =index('Auto control (sort)'!$A$1:$G$17 ; match($A2 ; 'Auto control (sort)'!$C$1 :$C$17 ; 0) ; 5) to determine who last took the car and =index(' Auto control (sort)'!$A$1:$G$17 ; match($A2 ; ' Auto control (sort)' !$C$1:$C$17 ; 0) ; 7) to determine the order status.
Also, I added a column with the status to the "Auto" sheet. The formula =iferror(ifs(B2 = "
I made all these changes to your table, so you can check everything there