IF/Match

Karrie Davis
Karrie Davis Overachievers Alumni

Good afternoon,

I'm having a problem with the below formula. I'm trying to do a formula that will check Invoice numbers on two sheets. If they match it will say yes, and if there is no match it will say no. However I'm getting Invalid Data type when there is a match, and No match if they don't match.

=IF(MATCH(Invoice65, {Exceptions}, 0), "yes", "no")


Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    MATCH outputs a numerical value, so you need to say that if the MATCH is greater than zero (meaning it is found within the range), then "yes", otherwise "no".

    =IF(MATCH(Invoice65, {Exceptions}, 0) > 0, "yes", "no")

  • I have a weird issue. Should i be using the IF/MATCH to narrow down the selection with a helper column?

    On a cross linked sheet, i am running an Index Match to match the Unique column value with another sheet's unique column value and bring in the data. It was working fine, but suddenly it became blank. Then i re-wrote the formula and saved / refreshed, the sheet flashed the results and then again goes blank....

    =IFERROR(INDEX({Daily Retrofit Intake Sheet Range 1}, MATCH($[Unique Column]@row, {Daily Retrofit Intake Sheet Range 2}, 0)), "")

    The sheet containing the formula

    Showing blank results

    The sheet that is being referenced and the 15/11/20 Riley record is being fetched. The result should be 3.

    Am i exceeding some cell linkage limit? I don't see any error anywhere in the activity log or otherwise.

    Regards,

    Jamal

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try removing the IFERROR part and see what pops up.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!