Finding the last occurrence of a value in a list

I have an action log that tracks my physical inventory by serial number, so one serial number could appear in multiple times in the list.

On a separate table, I want to be able to parse this list and show only the last action taken based no the serial number. I can assume the action list will be sorted by date.

This may not matter, but I do need to keep this file in .xls format, as I have a Python script that may need to read this file later on and extract data, and maybe even write to it.

I have attached a sample showing a mockup of the action log, the desired results, and my few attempts at solving the problem.

So far, I've had no luck. Could someone tell me how to do this or throw some advice my way?

Thanks,

JoshLookup.xls

Wow.

Simply, Wow. Thank you.

This doesn't look anything like the documentation.

I gather the 1/($B$3:$B$6=E3) is doing the job of reversing the array and doing the check? If so, what purpose does the 2 serve?

Hi Josh, I've placed a solution in a copy of your original spreadsheet.

121109 - Lookup Last Occurrence.xls

Oops...pipped to the post! ;-)

Originally Posted by Sofistikat

Oops...pipped to the post! ;-)

Is that an Australian phrase or an ExcelForum phrase? I've never heard of it.

I'm trying to figure this one out. I don't get what the equation is actually doing. THat is, I don't get the what the input to this equation is doing. B3:B6=E3 returns a boolean value, so 1/1 = 1 or 1/0 = Error. I suppose the 2 provides an upper limit, but how that identifies the last True value, I don't know.

Originally Posted by jeffreybrown

Hi Josh,

See if this helps.

Yup. That helps a lot.

Thanks.

Originally Posted by _Josh_

Is that an Australian phrase or an ExcelForum phrase? I've never heard of it.

The Queen's English, I believe :-)