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!
Answers

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
Ipshita Mukherjee

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

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)
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.8K Get Help
 410 Global Discussions
 219 Industry Talk
 457 Announcements
 4.7K Ideas & Feature Requests
 143 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 298 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!