INDEX MATCH or something else?

Nandafc

Hello everyone,

I need your help setting up this formula in Smartsheet, since we don't have the formula LOOKUP, as I have in excel.

I have this table, where every time I add the [Site] number: 301, 302 or 303, it will look for the last [Actual Visit End Date] for the specific site number, and bring the last day of the visit on the column [Last Visit Date] highlighted in yellow. However, the formula I added INDEX COLLECT is always bringing from the row = 1. Is it possible to create a dynamic formula for the row to look for the last?

Thank you in advance.

Fernanda

Carson Penticuff

Ahh... I see now, I misunderstood what you are attempting to do. Unfortunately, there is (to the best of my knowledge) no way to create a truly "dynamic" formula to do what you want. If you have the ability to add a helper column to your sheet, however, there is a workaround. I tend to shy away from autonumber columns, as they can create problems when inserting or deleting rows. For instances such as these, I prefer to create a manual number column. In this example, it is labeled [ID]. Simply create the column and manually drag it down to create sequential numbering to use as a reference in formulas. You can "prefill" this column with as many rows as you like.

=IF(MATCH(ID@row, COLLECT(ID:ID, Site:Site, Site@row), 0) > 1, INDEX(COLLECT([Actual Visit End Date]:[Actual Visit End Date], [Site]:[Site], [Site]@row), MATCH(ID@row, COLLECT(ID:ID, Site:Site, Site@row), 0) - 1), "")

Also of note, there is an extra IF() statement included to prevent the first instance of each Site from throwing an error since there will be no previous visit to pull a date from. Unfortunately, the IFERROR() function will not handle this.

Carson Penticuff

This should work as long as [Actual Visit End Date] is formatted as Date, which it appears to be. Instead of using INDEX, collect the values and grab MAX.

=MAX(COLLECT([Actual Visit End Date]:[Actual Visit End Date], [Site]:[Site], [Site]@row))

Nandafc

Hi @Carson Penticuff ,

Thank you for you help, however, now it is only collecting the last date for all the rows, and I need to be for every visit different.

So, one would be 15Sep2022, the other one 21Oct2022.

Carson Penticuff

Ahh... I see now, I misunderstood what you are attempting to do. Unfortunately, there is (to the best of my knowledge) no way to create a truly "dynamic" formula to do what you want. If you have the ability to add a helper column to your sheet, however, there is a workaround. I tend to shy away from autonumber columns, as they can create problems when inserting or deleting rows. For instances such as these, I prefer to create a manual number column. In this example, it is labeled [ID]. Simply create the column and manually drag it down to create sequential numbering to use as a reference in formulas. You can "prefill" this column with as many rows as you like.

=IF(MATCH(ID@row, COLLECT(ID:ID, Site:Site, Site@row), 0) > 1, INDEX(COLLECT([Actual Visit End Date]:[Actual Visit End Date], [Site]:[Site], [Site]@row), MATCH(ID@row, COLLECT(ID:ID, Site:Site, Site@row), 0) - 1), "")

Also of note, there is an extra IF() statement included to prevent the first instance of each Site from throwing an error since there will be no previous visit to pull a date from. Unfortunately, the IFERROR() function will not handle this.

Nandafc

Thank you so much! You are a genius. It worked. Do you know how to calculate how many days from the Actual start visit and actual end visit? Let's say 20Oct2022 and 21Oct2022 would be 2 days.

Thank you in advance.

Fernanda

Carson Penticuff

Will you be plugging that information into the [Days On-site] column? If so, this should work:

=IFERROR(NETDAYS([Actual Visit Start Date]@row, [Actual Visit End Date]@row), "")

Nandafc

Amazing! I appreciate so much your help.

Carson Penticuff

Happy to help 👍