Using VLOOKUP to return a most recent date but with conditions

Liam Burns

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.

VanessaK_SRR

You don't want a VLOOKUP. You want a MAXIF, but that is not a Smartsheet function, so you will need to create a nested IF functions, looking for MAX value in date column IF Name = DesiredName, then IF Quality = High.