Return Previous status

Raul Cabrera

@Genevieve P. or @Paul Newcome maybe you can help me with something. I have a sheet that i need to include some previous status column that i have in other sheet (probability Change log). I need that the status is the last row updated. In the sheet that i have the previous status i have the status and the "modified date" that will be the reference criteria. I already got to have in a column the last date of the item but now i need in other column show the Status (Low, High, Medium).

Probability change log.xlsx

in the main sheet i already got a column with the last date of update using the following formula

=MAX(COLLECT({Modified}, {Opp}, [Opportunity ID]@row))

Now i need that another column collect the "probability" status on the same file (sheet) accord with the last date that i got with the previous formula,

example

From this table the return value should be the last updated that means that need to evaluate the "modified" date to see which is the last one, respect with the "Opp" column and return "probability" that in this case is "High" (in green)

Thanks in advance.

Genevieve P.

Hi @Raul Cabrera

You can use an INDEX(COLLECT formula to use multiple matching criteria to bring back another cell. There's an example in this Help Article: Formula combinations for cross sheet references

In your case, the two criteria are the [Opportunity ID]@row and the Max Date you returning.

For example:

=INDEX(COLLECT({Probability Column}, {Opp}, [Opportunity ID]@row, {Modified}, [Date Column]@row), 1)

Let me know if that makes sense and works for you!

Cheers,

Genevieve

Genevieve P.

Hi @Raul Cabrera

You can use an INDEX(COLLECT formula to use multiple matching criteria to bring back another cell. There's an example in this Help Article: Formula combinations for cross sheet references

In your case, the two criteria are the [Opportunity ID]@row and the Max Date you returning.

For example:

=INDEX(COLLECT({Probability Column}, {Opp}, [Opportunity ID]@row, {Modified}, [Date Column]@row), 1)

Let me know if that makes sense and works for you!

Cheers,

Genevieve