Index Match formula returning #NOMATCH, can search type be a formula?


I am attempting to create an Index(Match( formula to pull job names based on a job number, but I am getting a #NO MATCH error. I have used this formula hundreds of times, so I am wondering if this is because my Match search type pulls from a cell that is also a formula?? Any workarounds?

This is my sheet, I want to use the job number to reference another sheet and pull the job name:

In example 1 for 19407, the job is not found under In Progress, but is in Closed.

If it matters, the formula in the Primary (JOBS) column is:

=IFERROR(LEFT(String@row, FIND(",", String@row) - 1), "")


Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Natalia Kataoka

    It looks like you formula may be turning the numerical values into a text string, which would then not be seen as a match in your source sheet if the Job No. is numerical.

    Try wrapping the VALUE function around that referenced cell, like so:

    =INDEX({Master - Closed Jobs - name}, MATCH(VALUE([Primary Column]@row), {Master - Closed Jobs - Job no}, 0))

    Let me know if this worked!



    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!