CODEWITHSUNDEEP
excelindexingexcel-formulastring-matching
Gipsy
Gipsy

Reputation: 81

How to get the last match of a string over a column and print the subsequent column's value in excel

I am forced to use excel for work reasons and I am a novice with that...so forgive me for the stupid question. I have an excel sheet like that: enter image description here

I would like to have in cell B53 (the green coloured one) the last satus registered in column F for SN0006 --> status "READY". So, steps are:

  1. Find all the occurrences that matches cell A53 content, id est SN0006, in the column B ("SN")
  2. Take the last occurrence (at the n-1 row). Dunno if it could help but consider that column "EVENT ID" is ordered in an ascending manner so that the last occurrence will have a higher index
  3. Print in cell B53 the content of the column "STATUS" respect to the last SN0006 occurrence row.

Until now I was able to print the STATUS of the first SN0006 occurrence "BRING UP" in that way:

=INDEX(F39:F51;MATCH(A53;B39:B51;0))

Thank you in advance for any help!

Upvotes: 1

Views: 1553

Answers (2)

Gipsy
Gipsy

Reputation: 81

Finally I also find a way but I didn't understand how it works...

=INDEX(F39:F51;MATCH(2;1/(B39:B51=A53)))

Upvotes: 1

Spectral Instance
Spectral Instance

Reputation: 2494

This is an array formula which will work in Excel 2007 and later

=INDEX(F39:F51,MAX((A53=B39:B51)*ROW(B39:B51))-38)

Upvotes: 3

Related Questions