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.

L_123

nested if is a lot of work. instead use a =max(collect(

 

collect will return an array based on conditions and you use the max to return the appropriate value. this should be pretty straightforward to make.

 

https://help.smartsheet.com/function/collect

Liam Burns

Thank you both. However unless I'm being silly, I don't think either Max or Collect suport cross-sheet formulas - unless I'm doing it wrong?

 

Cheers,

L_123

You can use collect across sheets. In fact I just did that yesterday. The max isn't technically across sheets, it max's the array grabbed by collect.