Some type of look up formula needed


Hello Experts!

I have a sheet that is compiling a lot of project information for P&L purposes. I need to find a formula that will enter the Project Manager for a Job number (each time it appears). I think the best place to pull that from is my project intake sheet. Please see below screenshots. Can someone recommend a formula to accomplish this?

Best Answer

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

    Hey @Micah Turner

    I would recommend an Index/Match for the lookup. For this specific instance, the formula below should work

    =INDEX({Project Intake Sheet Project Manager column}, MATCH([Job Number]@row, {Project Intake Sheet Job Number column},0))

    When referring to another sheet (cross sheet reference), once must build each reference physically using the formula window - one cannot simply copy paste this formula into your sheet. Your ranges will appear different - that is to be expected. I named them so you can see exactly what the range is.

    Will this work for you?


    If interested, this article provides information on cross sheet references


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!