How can I get the most recent value added to a column in relation to a specific value or condition?

I have an inventory monitoring system which contains information on who are the assignee of a specific equipment. For example, a laptop may have an assignee, and the system will have that information. But the laptop may have different assignees over time until it gets disposed. In my setup, I have two sheets, the first sheet (Sheet A) contains data about the equipment, i.e., an asset tag, the model/brand of the equipment, acquisition date. The second sheet (Sheet B) would contain the assignees for the equipment, i.e. the assignee name, assignee's group.

The common columns between the two sheets are the asset tag of the equipment, the assignee name, and the assignee's group. What I did was to use the functions INDEX and MATCH to get the assignee name for a particular equipment basing it on asset tag data. The formula is encoded in Sheet A for the column for the assignee and it gets the assignee value from Sheet B. The formula looks like this:

=INDEX({Sheet - Asset Tracker - Assignee Range 1}, MATCH([Asset Tag]@row, {Sheet - Asset Tracker - Assignee Range 2}, 0))

But I cannot get it to work in such a way that I get the most recent or the current assignee for a particular equipment, especially if the equipment has already been assigned/reassigned over time. Any ideas on how I can get this to work?

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!