Can VLOOKUP match on the most recent row of duplicate entries?


I know my question itself is confusing, so let me explain. I have configured data to come into Smartsheet from SharePoint via Power Automate. It is depositing some basic metadata into a "From SharePoint" sheet (image included here).

I am then doing a VLOOKUP against this sheet to populate some of that metadata on a master tracker sheet. My issue/question comes in when there are duplicate 'Request' numbers as highlighted above. The second instance of CMS_000-Q1 (created on 7/28) should be superseded by the second instance (created on 8/2). VLOOKUP functionality finds the first match and returns the results from that match. I need to have it match on the most recent instance. As you can see, -Q3 has three instances already, and I need to ensure that I am getting the most recent information in my tracker sheet.

Any thoughts on how I might be able to accomplish this?

  • It does not appear that I can direct Power Automate to add the new rows at the top of the sheet (similar to how you can in Smartsheet forms), but I am going to pursue that option further with my SharePoint SME.
  • Are INDEX/MATCH/COLLECT an option here? I have not used them before--I am used to VLOOKUP and it has met my needs in the past.
  • Any other alternatives you can think of?

I appreciate any and all suggestions.




Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!