How to return the most recent text for a given value?

Victor.Silva

How can I return the most recent text from a cell, setting a criterion? I tried to use the formula =INDEX(), MATCH() -1, I understood that the "-1" in the formula would return values in descending order, but when I apply the formula the column repeats the same value found in the cell where I applied the formula to the others. I tried to use the MAX(COLLECT) function, but it only returns numbers, not text.

Kelly Moore

Hey @Victor.Silva

You will need a helper column to accomplish your task. A screenshot of both sheets would have allowed my suggestion to be more specific.

The helper column will identify what row is the max, using whatever criteria determines that for you. Whether this helper column is on the source sheet (maybe as a checkbox column) or on the destination sheet is dependent on exactly what is needed and how the data (on both sheets) are structured. Personally, if I can get away with a checkbox column on the source sheet that indicates this row is the Max, I do that. Typically, to find a Max you are referencing a system column - either the Created (date) or the [Row ID]- as you found out MAX requires a date or a numeric value to work. You can use the Modified field but realize many actions on a sheet will cause that field to change, not just the action you may be intending.

For the final data, I will use either an Index/Match (with zero as the sort), or INDEX/COLLECT if I need multiple criteria.

If you provide screenshots, I will attempt more specific advice, if needed.

Kelly