Help getting value to appear

Here is my equation. I am trying to get it to match the columns between two spreadsheets and display a value based on cells matching. It's not giving me an error message and it looks like all the references are correct, but it's not displaying a result.


=IFERROR(INDEX({PVSYST Forecast}, MATCH(1, ([Project Name]@row = {Project Name}) * (Month@row = {Month}), 0)), "")


This is the sheet the equation is on. Asking it to look at these cells...

Then I am asking it to compare to these columns, find a row that has a matching data within Month and Project Name and then to pull the result where we have a match in the row. The result should pull the PVSYST forcast column

Here is where it's supposed to be displaying.


What am I doing wrong? I am sitting here with CHAT GBT for hours trying to figure it out and have tried it all different ways to work.

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    @NuGenAdmin

    An INDEX/MATCH has a specific format. The formula above is not written in the expected format. The IFERROR is masking the error as you have it written to produce a blank field when errors are encountered.

    Try this

    =INDEX(COLLECT({PVSYST Forecast}, {Project Name},[Project Name]@row,{Month},Month@row),1)

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    @NuGenAdmin

    An INDEX/MATCH has a specific format. The formula above is not written in the expected format. The IFERROR is masking the error as you have it written to produce a blank field when errors are encountered.

    Try this

    =INDEX(COLLECT({PVSYST Forecast}, {Project Name},[Project Name]@row,{Month},Month@row),1)

    Kelly

  • NuGenAdmin
    NuGenAdmin ✭✭✭

    I had to make a small adjustment, but I got it to work. You're a lifesaver!!! Thank you! Do you have suggestions on a place I get learn to get better with formulas? @Kelly Moore

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!