INDEX MATCH with IF function


Hello smartsheet community, I am stuck!

The below shows SHEET 1. There is an automation on this sheet that copies all rows on the 27th of the month to SHEET 2.

I then want to pull the data from the load column back from SHEET 2, to SHEET 1.

As the 'Date Added' column has a new month every month, the data will be pulled into the Jan, Feb, Mar etc column to give me the 'Load' value for each month.

There is another column that registers when the Date Added column is TODAYs month. This is in a hidden column called 'Jan Formula'/'Feb Formula'/'Mar formula' etc. This returns "1" when it is this month (In SHEET 1 this number changes based as the Date Added column is changed. In SHEET 2, this number is fixed, which is why I am copying the rows to another sheet)

The formula I have is

=INDEX(COLLECT({Load.}, {Rack#}, [Rack #]@row, {Outlets}, Outlets@row, {A/B}, [A / B]@row, {U /L}, [U / L]@row))

I want to collect "Load" (from sheet 2) when the above criteria are met, and when Jan formula (in Sheet 2) = 1

I.E. the Jan column should return the load value that was copied to SHEET 2 on the 27th January where the Rack#, Outlet, A/B, U/L MATCH and where 'Jan Formula' = 1 (In Sheet 2)

Sorry for long description. I hope some of it makes sense. Any help appreciated!



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!