help with my Index Match formula.

System

This discussion was created from comments split from: INDEX MATCH error.

Edidiong Obot

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"

1. =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)"

Paul Newcome

Try an INDEX/COLLECT instead. The COLLECT function would be filled in exactly like the SUMIFS.

=INDEX(COLLECT(............), 1)