INDEX MATCH with IF function

Sam Swain

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!

Ipshita

Hello @Sam Swain The first (tiny) error I can note on your formula is the word Load. ends with the period, you don't seem to have the period (.) on your column name.

Try fixing that and test your formula again.

Hope this helps,

Cheers!

Ipshita

Sam Swain

Hi Ipshita, thanks! But that's just the reference name from the other sheet 2. I've removed the (.) but hasn't worked...

Leibel S

@Sam Swain

Just add the Jan criteria into the collect:

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