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
Check out the Formula Handbook template!