How to look at multiple columns in a row and return the last "Not Blank" cells in the current cell

Tracey Tume

I am wanting to populate the last date that a quote was done (Quoted Price Option date) into the "Date Last Quoted Price" column. Is there a formula that will do this? Every row will have the last date a quote was done in a different column.

Thanks for your help!

Tracey

Kelly Moore

Hello @Tracey Tume

Try this

=INDEX([Quote Price incl GST]@row:[Quoted Price Option 10]@row, 1, COUNTIFS([Quote Price incl GST]@row:[Quoted Price Option 10]@row, <>""))

This approach uses the optional column index portion of the INDEX function and assumes the column entries are always sequential moving across your sheet. We use the COUNTIFS to get a count of non-blank cells in the row. This COUNTIFS value is used to indicate the column number that you wish to pull in. If the screenshot isn't showing all of the columns, beginning at the first column up to the columns you need, you'll need to add the quantity of columns that are to the left of the columns shown in the screenshot to the COUNTIFS . For example 5+COUNTIFS([column name]:[column names], <>""). Columns to the right of these columns do not need to be added.

Does this work for you?

Kelly

https://help.smartsheet.com/function/index#:~:text=Returns%20an%20item%20from%20a,provided%20row%20and%20column%20indexes.&text=The%20group%20of%20cells%20that%20you%20want%20to%20evaluate.&text=The%20row%20position%20(used%20in,of%20the%20item%20to%20return.