help with my Index Match formula.

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

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"

    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
    Paul Newcome ✭✭✭✭✭✭

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

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!