Using VLOOKUP to return a most recent date but with conditions

Liam Burns
edited 12/09/19 in Formulas and Functions

Hi there,

I'm looking to pull off the following:

  • Smartsheet A is a list of stakeholders we regularly engage with. Every row is an individual stakeholder
  • Samrtsheet B is a tracker where we list the engagements themselves. Every row is an occasion where we have interacted with a stakeholder. We categorise these engagements as high, medium and low quality, and record the date of the engagement
  • I want to create a formula in Smartsheet A that looks up Smartsheet B for the most recent *high* quality engagement that has happened for the stakeholder in that row in Smartsheet A, and return the date into Smartsheet A.

So I think I need a formula that:

  • Checks the name recorded in the same row within Smartsheet A
  • Finds all the mentions of that name in Smartsheet B where "Quality" is "High"
  • Picks the most recent occassion
  • Returns the date of that occassion recorded in Smartsheet B into Smartsheet A

Any help appreciated.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!