help with my Index Match formula.
Answers
-
Hi all,
I need help with my Index Match formula.
So I want to collect Invoice values for different months (Example "Invoice July'23" column) and I used the formula below but it returns "No Match"
- =INDEX([Invoice Value]:[Invoice Value], MATCH(IFERROR(MONTH(@cell), 0) = 7, [Planned Finish]:[Planned Finish], 0))
Additionally, any suggestions as to how to add year to the formula, so that I only pull data for a specific year.
Below is the formula that I am currently use to get the sum of all the values per month for each year by replacing 7 and 2023 with the the corresponding values for the months and year respectively. However, I need to of double check for correctness. Hence the need to pull the data using 1 above.
"=SUMIFS([Invoice Value]:[Invoice Value], [Planned Finish]:[Planned Finish], IFERROR(MONTH(@cell), 0) = 7, [Planned Finish]:[Planned Finish], IFERROR(YEAR(@cell), 0) = 2023)"
-
Try an INDEX/COLLECT instead. The COLLECT function would be filled in exactly like the SUMIFS.
=INDEX(COLLECT(............), 1)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K 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!