Automate Index / Match forumla - add an IF statement? or another Match??
Hi,
I have a report that I will be data dumping into monthly (1st pic called "Jan GSS"). I created an Index/Match formula (in second pic) to pull those numbers over into my main sheet (GSS ITRec column):
=INDEX({Jan GSS ITR}, MATCH(Marsha@row, {Jan GSS MARSHA}, 0))
How can I make this formula better so that when I add the Feb and March and so on data to the sheet "Jan GSS" it will update the approprate month rows in main sheet for each property (pic 2)?
That way I can apply the formula to all the cells and don't have to update it monthly. Thank you!!
Best Answer
-
Hi Ann,
Based on how you have your first sheet set up (Jan GSS), you would need to update the column reference for each month and create an individual formula per-column in the source sheet & month in your destination sheet, like so:
January
=INDEX({Jan GSS ITR}, MATCH(Marsha@row, {Jan GSS MARSHA}, 0))
February
=INDEX({Jan GSS ITR}, MATCH(Marsha@row, {Feb Column GSS MARSHA}, 0))
March
=INDEX({Jan GSS ITR}, MATCH(Marsha@row, {March Column GSS MARSHA}, 0))
... etc
You would only need 12 of these (since I presume your Property 2 has a different Marsha@row than Property 1). Additionally, you can set these up now, so when you have new data you won't need to remember to add it in.
Since there is nothing to currently match in these months, you'll receive a NOMATCH error. To eliminate this, you can add an IFERROR function around your current formula, like so:
=IFERROR(INDEX({Jan GSS ITR}, MATCH(Marsha@row, {Jan GSS MARSHA}, 0)), "Not Started")
I have this saying "Not Started" but you could keep it blank by using "" instead.
Let me know if you have any questions about this!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
Hi Ann,
Based on how you have your first sheet set up (Jan GSS), you would need to update the column reference for each month and create an individual formula per-column in the source sheet & month in your destination sheet, like so:
January
=INDEX({Jan GSS ITR}, MATCH(Marsha@row, {Jan GSS MARSHA}, 0))
February
=INDEX({Jan GSS ITR}, MATCH(Marsha@row, {Feb Column GSS MARSHA}, 0))
March
=INDEX({Jan GSS ITR}, MATCH(Marsha@row, {March Column GSS MARSHA}, 0))
... etc
You would only need 12 of these (since I presume your Property 2 has a different Marsha@row than Property 1). Additionally, you can set these up now, so when you have new data you won't need to remember to add it in.
Since there is nothing to currently match in these months, you'll receive a NOMATCH error. To eliminate this, you can add an IFERROR function around your current formula, like so:
=IFERROR(INDEX({Jan GSS ITR}, MATCH(Marsha@row, {Jan GSS MARSHA}, 0)), "Not Started")
I have this saying "Not Started" but you could keep it blank by using "" instead.
Let me know if you have any questions about this!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 454 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!